package cn.com.nes.site.module.dao;

import cn.com.nes.common.em.platform.CallMethodEnum;
import cn.com.nes.common.em.platform.PlatformEnum;
import cn.com.nes.common.util.*;
import cn.com.nes.common.util.id.IdUtils;
import cn.com.nes.mybatis.agent.system.entity.Department;
import cn.com.nes.mybatis.agent.system.entity.TUser;
import cn.com.nes.mybatis.agent.system.mapper.TUserMapper;
import cn.com.nes.mybatis.callcenter.mapper.ParamParamMapper;
import cn.com.nes.site.config.CustomConfigurationProperties;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.MallUserInfo;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.entity.UserInfo;
import cn.com.nes.site.entity.mtpf.MtpfToken;
import cn.com.nes.site.entity.vo.system.DepartmentSaveVO;
import cn.com.nes.site.entity.vo.user.PushUserInfoVO;
import cn.com.nes.site.entity.vo.user.SaveBusinessCompanyVo;
import cn.com.nes.site.service.system.company.DepartmentService;
import cn.com.nes.site.service.system.company.TAgentIntentApplicationService;
import cn.com.nes.site.service.system.user.SystemUserService;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.nosql.redis.RedisDS;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.jsoup.internal.StringUtil;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;
import redis.clients.jedis.Jedis;

import javax.annotation.Resource;
import java.sql.*;
import java.util.Date;
import java.util.*;

@Slf4j
@Repository
@EnableAsync
public class UserDaoImpl extends BaseDaoImpl {

	private TransactionStatus newg_status = null;
	@Resource
	private TUserMapper tUserMapper;

	@Resource
	private ParamParamMapper paramParamMapper;

	@Resource
	private SystemUserService systemUserService;

	@Resource
	private TAgentIntentApplicationService tAgentIntentApplicationService;

	@Resource
	DepartmentService departmentService;

	//获取商城用户信息
	public String getShopUser(String phone) {
		String token = "";
		// 请求商城登录接口
		final Jedis jedis = RedisDS.create().getJedis();
		try {
			// 生成验证码
			jedis.set("sms:validate:code:" + phone, "999999");
			JSONObject params = new JSONObject();
			params.put("phone", phone);
			params.put("captcha", "999999");
			// 注册用户
			final String response = HttpUtils.sendJSONPost(Globals.SHOP_REGISTER_URL, params.toJSONString());
			token = JSONObject.parseObject(response).getJSONObject("data").getString("token");
		} catch (Exception e) {
			log.warn("获取商城用户信息失败：{}", e.getMessage());
		} finally {
			jedis.close();
		}
		return token;
	}

	// 获取商城用户信息
//	public String getShopUser(String phone, String password) {
//		String token = "";
//		String shopUserId = "";
//		Connection conn_newmall = null;
//		try {
//			conn_newmall = DBUitl.getDBConn_newmall();
//			String sb = " select id from eb_user where phone = ? and is_logoff=0 ";
//			List<Map<String, Object>> listTmp = this.queryForList(conn_newmall, sb.toString(), new Object[]{phone});
//			if (listTmp.size() > 0) {//如果有添加用户信息
//				shopUserId=listTmp.get(0).get("id").toString();
//			} else {
//				try {
//					//不存商城用户，请求商城接口，创建新用户
//					String sbadd =" INSERT INTO `eb_user`(`phone`,`account`,`pwd`) VALUES ('"+phone+"','app用户','123456') ";
//					newg_status = begin_mall();// 开启事物
//					this.getJdbcTemplate_mall().update(sbadd.toString(), new Object[]{});
//					commit_mall(newg_status);
//				} catch (Exception e) {
//					rollback_mall(newg_status);
//					e.printStackTrace();
//				}
//				//注册完查询
//				listTmp = this.queryForList(conn_newmall, sb.toString(), new Object[]{phone});
//				shopUserId=listTmp.get(0).get("id").toString();
//			}
//			token = new JwtUtil().createShopJWT(Integer.parseInt(shopUserId));
//		} catch (Exception ignored) {
//			ignored.printStackTrace();
//		} finally {
//			try {
//				conn_newmall.close();
//			} catch (SQLException e) {
//				e.printStackTrace();
//			}
//		}
//		return token;
//	}

	public PageData checkedUsers(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("uuid不能为空");
		TUser tUser=tUserMapper.getcallCenterUserId(param.get("uuid").toString());
		if(ObjectUtil.isNotNull(tUser)) {
			if (ObjectUtil.isNotEmpty(tUser.getCallCenterUserId())) {
				res.setRetCode(0);
				res.setRetMessage("查询成功");
			} else {
				res.setRetCode(9);
				res.setRetMessage("未申请客服中心");
			}
		}else{
			res.setRetCode(9);
			res.setRetMessage("未查到此用戶");

		}
		return res;
	}
	public PageData login(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("用户名或者密码错误");
		StringBuffer sb = new StringBuffer();
		try {

			sb.append("select uuid,password,phone,expirationdate,now(),if(expirationdate<now(),'1','0') sfygq from t_user where ( username=? or (phone=? and phone is not null and phone <>'')) and sfsc=0 ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] { param.get("username"),param.get("username") });
			if (list.size() == 0) {
				res.setRetCode(9);
				res.setRetMessage("用户不存在");
				return res;
			}else{
				String sfygq=(String) list.get(0).get("sfygq");
				if("1".equals(sfygq)){
					res.setRetCode(9);
					res.setRetMessage("用户有效期已过");
					return res;
				}
			}

			String md5 = (String) list.get(0).get("password");
			String password = (String) param.get("password");
			String uuid = list.get(0).get("uuid").toString();
			String phone = (String) list.get(0).get("phone");
			if(StringUtils.isEmpty(phone)) {
				phone = (String) param.get("username");
			}

			// boolean passwordValidity = PasswordUtils.passwordValidity(password, md5);
			// if (!passwordValidity) {
			// 	res.setRetCode(9);
			// 	res.setRetMessage("用户密码不正确");
			// 	return res;
			// }
			boolean isOK = EncryptedUtil.verify(password, md5);
			if (!isOK) {
				res.setRetCode(9);
				res.setRetMessage("用户密码不正确");
				return res;
			}


			// 获取用户信息
			param.put("uuid", uuid);
			res = getUserInfo(req);
			//res.getData().put("password", password);//去掉密码，防止暴露
			res.getData().put("platform", PlatformEnum._agent.getCode());
			String role_id = (String) res.getData().get("role_id");
			req.getReqData().put("role_id", role_id);
			req.getReqData().put("companyType", res.getData().get("companyType"));
			String companyId=res.getData().get("companyId").toString();
			// 生成jwt信息
			JwtUtil util = new JwtUtil();
			String key = util.createJWT((String) res.getData().get("username"),
					JsonUtil.getStriFromObj(res.getData()), Globals.TOKEN_TIME);
			res.getData().put("key", key);

			//创今云暖key

			String tmpStr = HttpUtils.sendPost(Globals.HEAT_LOGIN_URL, Globals.HEAT_LOGIN_PARAM);

			PageData heatinfo = (PageData) JsonUtil.getObjFromStr(tmpStr, PageData.class) ;
			if(heatinfo!=null && heatinfo.getRetCode()==0 && heatinfo.getPage().size()>0) {
				String heat_token = (String) heatinfo.getPage().get(0).get("token") ;
				res.getData().put("heat_token", heat_token);
			}

			//厂商key
			// 获取厂商系统的key
			try {
				String mfepuser_workorder = (String) res.getData().get("mfepuser_workorder");
				String mfepuser = (String) res.getData().get("mfepuser");

				if(StringUtils.isNotEmpty(mfepuser_workorder)) {
					tmpStr = HttpUtils.submitDataByDoPostJson(mfepuser_workorder, CustomConfigurationProperties.getMtpfUrl() + Globals.MTPF_LOGINURL) ;
				}else {
					if(StringUtils.isNotEmpty(mfepuser)) {
						tmpStr = HttpUtils.submitDataByDoPostJson(mfepuser, CustomConfigurationProperties.getMtpfUrl() + Globals.MTPF_LOGINURL) ;
					}else {
						tmpStr = HttpUtils.submitDataByDoPostJson(Globals.MTPF_LOGINUSER, CustomConfigurationProperties.getMtpfUrl() + Globals.MTPF_LOGINURL) ;
					}
				}

				MtpfToken mtpftoken = (MtpfToken) JsonUtil.getObjFromStr(tmpStr, MtpfToken.class);
				if (mtpftoken != null && mtpftoken.getRetCode() == 0) {
					String token = mtpftoken.getPage().get(0).getToken();
					res.getData().put("mfep_token", token);
				}
			}catch (Exception e) {
				// System.out.println("获取厂商key失败！");
				// e.printStackTrace();
				log.error("获取厂商系统token失败：{}", e.getMessage());
			}

			// 商城token
			//String shopToken = getShopUser(phone,(String) param.get("password"));
			String shopToken = getShopUser(phone);
			res.getData().put("shopToken", shopToken);

			//res.setKey(key);
			res.getData().put("password", null);

			PageData mo = getModelMenu(req);
			res.getData().put("model",mo.getPage());
			//res.setModel(mo.getPage());
			res.setFirstPageURL(mo.getFirstPageURL());

			PageData extData = getDict(req,companyId) ;
			res.getData().put("extData", extData.getData());
			//res.setExtData(extData.getData()) ;

			res.setRetCode(0);
			res.setRetMessage("OK");

		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getShopToken(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取商城用户token失败");
		try {
			String phone = (String) req.getReqData().get("phone");
			//String shopUser = getShopUser(phone, "PWD" + phone + "abc");
			String shopUser = getShopUser(phone);
			Map<String, Object> map = new HashMap<>();
			map.put("shopToken", shopUser);
			res.setData(map);
			res.setRetCode(0);
			res.setRetMessage("ok");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	@SuppressWarnings("unchecked")
	public PageData getKeyByToken(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("用户名或者密码错误");
		StringBuffer sb = new StringBuffer();
		try {

			sb.append("select * from t_autologin where tokenID=? ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] { param.get("tokenID") });
			if (list.size() == 0) {
				res.setRetCode(9);
				res.setRetMessage("用户不存在");
				return res;
			}
			JSONObject jsonObject = (JSONObject) JSON.parse((String)list.get(0).get("key"));
			Map<String, Object> map = JSONObject.parseObject(jsonObject.toJSONString(), Map.class);
			res.setData(map);
			res.setRetCode(0);
			res.setRetMessage("OK");

		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getModelMenu(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取失败");
		StringBuffer sb = new StringBuffer();

		try {
			String role_id = (String) param.get("role_id");
			String companyType = (String) param.get("companyType") ;
			sb.append(" SELECT * from ( ");
			sb.append(" SELECT 1 isCheck,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,a.sort,3 level,b.isWrite from t_model a INNER JOIN t_role_model b on a.model_id=b.model_id and length( b.model_id)=6 and b.role_id=? and b.sfsc='0' ");
			sb.append(" where (a.type in ('00',?) or ?='00') ");
			sb.append(" union ");
			sb.append(" SELECT 1 isCheck,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,a.sort,2 level,b.isWrite from t_model a INNER JOIN t_role_model b on a.model_id=SUBSTR(b.model_id,1,4) and length(b.model_id)!=2 and b.role_id=? and b.sfsc='0' ");
			sb.append(" where (a.type in ('00',?) or ?='00') GROUP BY a.model_id ");
			sb.append(" union ");
			sb.append(" SELECT 1 isCheck,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,a.sort,1 level,b.isWrite from t_model a INNER JOIN t_role_model b on a.model_id=SUBSTR(b.model_id,1,2) and length(b.model_id)!=2 and b.role_id=? and b.sfsc='0' ");
			sb.append(" where (a.type in ('00',?) or ?='00') GROUP BY a.model_id ");
			sb.append(" ) m  ");
			sb.append(" ORDER BY m.sort,m.model_id,m.level ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] { role_id, companyType,companyType,role_id ,companyType,companyType, role_id,companyType,companyType});

			// 转换list
			List<Map<String, Object>> list1 = new ArrayList<Map<String, Object>>();
			List<Map<String, Object>> list2 = new ArrayList<Map<String, Object>>();
			List<Map<String, Object>> list3 = null;
			Map<String, Object> Menu1 = null;
			int i=0;
			String pageURL="";
			for (Map<String, Object> data1 : list) {
				Long level1= (Long) data1.get("level");
				if(level1 == 1) {
					Map<String, Object> menu2 = null;
					for (Map<String, Object> data2 : list) {
						Long level2 = (Long) data2.get("level");
						if (level2 == 2) {
							if(data2.get("g_model_id").equals(data1.get("g_model_id"))){
								menu2 = data2;
								list2.add(menu2);
								list3 = new ArrayList<Map<String, Object>>();
								menu2.put("submenu", list3);
							}
						}
						if(level2 == 3) {
							if(data2.get("g_model_id").equals(data1.get("g_model_id"))){
								if (i==0){
									pageURL=data2.get("url")+"?mcode="+data2.get("model_id")+"&mname="+data2.get("name")+"&mtype=";
									i=1;
								}
								list3.add(data2);
							}
						}
					}
					Menu1 = data1;
					Menu1.put("submenu", list2);
					list1.add(Menu1);
					list2 = new ArrayList<Map<String, Object>>();
			   }
			}

			res.setFirstPageURL(pageURL);
			res.setPage(list1);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getDict(DataReq req,String companyId) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取失败");
		StringBuffer sb = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		try {
			req.setPagesize(2000);
			sb.append(" SELECT param_type,type_name,name,value from  t_param where is_delete=0 " );
			sb.append(" and (companyId='' or companyId is null or companyId=?) ");
			sb.append(" ORDER BY sort,param_type,value ");
			// 分页
			sbfrom.append(sb.toString());
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,new Object[] {companyId});
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[] {companyId});
			list.addAll(paramParamMapper.getParamsTypeValue());
			// 转换list
			List<Map<String, Object>> distList = new ArrayList<Map<String, Object>>();
			List<Map<String, Object>> subList = null;

			Map<String, Object> firstType = null;
			Map<String, Object> dictMap = new HashMap<String, Object>();
			for (Map<String, Object> data : list) {
				boolean isIn = false;
				for (Map<String, Object> mainType : distList) {
					if (mainType.get("param_type").equals(
							data.get("param_type"))) {
						isIn = true;
					}
				}
				Map<String, Object> item = new HashMap<String, Object>();
				item.put("name", data.get("name"));
				item.put("value", data.get("value"));

				if (!isIn) {
					firstType = new HashMap<String, Object>();
					firstType.put("param_type", data.get("param_type"));
					firstType.put("type_name", data.get("type_name"));
					distList.add(firstType);
					subList = new ArrayList<Map<String, Object>>();
					firstType.put("child", subList);
					subList.add(item);
					//长效管护的字典需要根据param_type查询，热练云根据type_name查询
					if(ObjectUtil.isNotEmpty(data.get("bs"))){
						dictMap.put((String) data.get("param_type"), subList);
					}else{
						dictMap.put((String) data.get("type_name"), subList);
					}

				} else {
					subList.add(item);
				}
			}
			res.setPagecount(null);
			res.setPagesize(null);
			res.setRecordcount(null);
			res.setPageid(null);
			res.setData(dictMap);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getUserInfo(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取用户信息失败");
		param.put("tableName", "t_user");
		param.put("keys", "uuid");
		try {
			StringBuffer sb = new StringBuffer();
			sb.append(" SELECT d.isAuthorRole,d.`name` roleName,group_concat(h.projectId) projectIds,b.nes_companyId,b.companyName,b.companyType,c.`name` companyTypeName,b.pbxURL," +
					"b.isCert,a.id,a.uuid,a.username,a.sex,a.nickName,a.role_id,a.areacode,a.areaname,a.companyId,a.departmentId,a.identity," +
					"a.userType,a.openId,a.phone,a.email,a.wechat,a.longitude,a.latitude,a.createUid,a.createTime as createTimeString,UNIX_TIMESTAMP(a.createTime) createTime,a.modifyUid,a.modifyTime modifyTimeString,UNIX_TIMESTAMP(a.modifyTime) modifyTime," +
					"a.isassociated,a.relevanceid,a.sfsc,a.isOnlineReg,a.pic,a.mfepuser,a.open_id_dy,a.mfepuser_workorder,b.cc_companyId as callCenterCompanyId,a.call_center_user_id callCenterUserId");
			sb.append(" FROM t_user a ");
			sb.append(" LEFT JOIN t_company b on a.companyId=b.companyId ");
			sb.append(" LEFT JOIN (SELECT `NAME`,`VALUE` FROM t_param where param_type='95')c on b.companyType=c.`value` ");
			sb.append(" LEFT JOIN t_user_project h on a.uuid=h.userId AND h.sfsc<>'1' ");
			sb.append(" LEFT JOIN t_project i on h.projectId=i.id ");
			sb.append(" LEFT JOIN t_role d on a.role_id=d.role_id ");
			sb.append(" WHERE a.uuid=? and a.sfsc<>'1' ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] { param.get("uuid")});
			if (list.size() == 1) {
				res.setData(list.get(0));
				res.setRetCode(0);
				res.setRetMessage("OK");
			} else {
				res.setRetCode(9);
				res.setRetMessage("没有查询到该用户信息！");
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getUsers(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取用户信息失败");
		try {

			StringBuffer sb = new StringBuffer();
			StringBuffer sbfrom = new StringBuffer() ;
			Integer nesBind = ParameterUtil.getInteger(req, "nesBind");

			// 当勾选未绑定厂商时，查询公司列表
			if (ObjectUtil.isNotNull(nesBind)) {
				sb.append("select tc.companyId, tc.companyName, d.`value` companyType, d.`name` companyTypeName, if(tc.nes_companyId is null or tc.nes_companyId = '', '未绑定', '已绑定') as nesBind  ");
				sb.append(" FROM t_company tc ");
				sb.append(" LEFT JOIN (SELECT `name`,`value` FROM t_param WHERE type_name = '用户类型') d on tc.companyType=d.`value` ");
				sb.append(" where tc.sfsc = 0 and tc.companyType = '01' and (tc.nes_companyId is null or tc.nes_companyId = '') ");
				if (!StringUtils.isEmpty((String)param.get("username"))){
					sb.append(" and tc.companyName like '%").append(param.get("username")).append("%' ") ;
				}
				sbfrom.delete(0, sbfrom.length());
				sbfrom.append(sb.toString()) ;
				int idx=sbfrom.indexOf(" FROM ") ;
				sbfrom.delete(0, idx);
				Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{}) ;
				sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;
				List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
				res.setPage(list);
				res.setRetCode(0);
				res.setRetMessage("OK");
				return res;
			}

			sb.delete(0, sb.length());
			sb.append(" SELECT * FROM ( ");
			sb.append(" SELECT DATE_FORMAT(a.createTime,'%Y-%m-%d %H-%m-%s') createTime,a.id,a.uuid,a.username,a.nickName,if(a.expirationdate is null,'',a.expirationdate) as expirationdate,c.`value` sex,c.`name` sexName,a.phone," +
					"e.`value` userType,e.`name` userTypeName,a.role_id,b.`name` roleName," +
					"f.companyId,f.companyName,f.nes_companyId,d.`value` companyType,d.`name` companyTypeName,g.departmentId,g.departmentName," +
					"a.areacode,a.areaname,a.isassociated,a.relevanceid,a.openId, if(f.nes_companyId is null or f.nes_companyId = '', '未绑定', '已绑定') as nesBind," +
					"if(group_concat(h.projectId) is null,'',group_concat(h.projectId)) projectId,group_concat(i.project_name) projectName ");
			sb.append(" , case when a.is_foreman is null then 0 else a.is_foreman end as isForeman");
			sb.append(" FROM t_user a ");
			sb.append(" LEFT JOIN t_role b ON a.role_id = b.role_id ");
			sb.append(" LEFT JOIN t_company f ON a.companyId = f.companyId ");
			sb.append(" LEFT JOIN (SELECT `name`,`value` FROM t_param WHERE type_name = '性别') c on a.sex=c.`value` ");
			sb.append(" LEFT JOIN (SELECT `name`,`value` FROM t_param WHERE type_name = '用户类型') d on f.companyType=d.`value` ");
			sb.append(" LEFT JOIN (SELECT `name`,`value` FROM t_param WHERE type_name = '用户职务') e on a.userType=e.`value` ");
			sb.append(" LEFT JOIN t_department g ON a.departmentId = g.departmentId ");
			sb.append(" LEFT JOIN t_user_project h on a.uuid=h.userId AND h.sfsc<>'1' ");
			sb.append(" LEFT JOIN t_project i on h.projectId=i.id ");
			sb.append(" WHERE a.sfsc<>'1' and a.id<> '").append(req.getUinfo().getId()).append("' ");
			if (!StringUtils.isEmpty(req.getUinfo().getCompanyId())&&!"0000".equals(req.getUinfo().getCompanyId())){
				sb.append(" and a.companyId like '").append(req.getUinfo().getCompanyId()).append("%' ") ;
				//sb.append(" and a.companyId in ( ").append(getAllNextCompanyId(req.getUinfo().getCompanyId())).append(" ) ");
			}
			if (!StringUtils.isEmpty(req.getUinfo().getAreacode())&&!"86".equals(req.getUinfo().getAreacode())){
				//sb.append(getQueryXzqh(req.getUinfo().getAreacode(),"a.areacode"));
			}
			if (!StringUtils.isEmpty((String)param.get("treeId"))&&!"00".equals(param.get("treeId"))){
				String treeCompanyType=(String)param.get("treeCompanyType");
				if("00".equals(treeCompanyType)){
					sb.append(" and a.departmentId like '").append(param.get("treeId")).append("%' ") ;
				}else{
					sb.append(" and a.companyId like '").append(param.get("treeId")).append("%' ") ;
				}
			}
			if (!StringUtils.isEmpty((String)param.get("username"))){
				sb.append(" and (a.username like '%").append(param.get("username")).append("%' ") ;
				sb.append(" or a.nickname like '%").append(param.get("username")).append("%') ");
			}
			if (!StringUtils.isEmpty((String)param.get("phone"))){
				sb.append(" and a.phone like '%").append(param.get("phone")).append("%' ") ;
			}
			if (!StringUtils.isEmpty((String)param.get("companyType"))){
				sb.append(" and f.companyType ='").append(param.get("companyType")).append("' ") ;
			}
			if (!StringUtils.isEmpty((String)param.get("userType"))){
				sb.append(" and a.userType ='").append(param.get("userType")).append("' ") ;
			}
			if (!StringUtils.isEmpty((String)param.get("isOnlineReg"))){//在线注册用户
				sb.append(" and a.isOnlineReg = '").append(param.get("isOnlineReg")).append("' ") ;
			}
			// if (ObjectUtil.isNotNull(nesBind)) {
			// 	sb.append(" and (f.nes_companyId is null or f.nes_companyId = '') ");
			// 	sb.append(" and f.companyType ='01' ") ;
			// } else {
			// 	if (!StringUtils.isEmpty((String)param.get("isOnlineReg"))){//在线注册用户
			// 		sb.append(" and a.isOnlineReg = '").append(param.get("isOnlineReg")).append("' ") ;
			// 	}
			// }
			sb.append(" GROUP BY a.uuid ");
			sb.append(" )x ");
			//分页
			sb = DBUtils.sql_check(sb) ;
			sbfrom.delete(0, sbfrom.length());
			sbfrom.append(sb.toString()) ;
			int idx=sbfrom.indexOf(" FROM ") ;
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{}) ;
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;
		    List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});

			for(int i=0;i<list.size();i++){
				//封装项目
				String project=list.get(i).get("projectId").toString();
				if(!"".equals(project)&&""!=project){
					String[] project1=project.split(",");
					Integer[] project2=new Integer[project1.length];
					for(int j=0;j<project1.length;j++){
						project2[j]=Integer.parseInt(project1[j]);
					}
					list.get(i).put("projectId", project2);
				}
			}

			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getUserMesg(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取个人信息失败");
		try {

			StringBuffer sb = new StringBuffer();

			sb.append("select b.mer_img,b.mer_logo,b.mer_id,b.mer_name,b.mer_scale,b.mer_business,b.mer_introduction,b.mer_timeStart,b.mer_timeEnd,b.mer_isShowPrize," +
					"a.id,a.uuid,a.username,b.isCert,a.createTime,a.pic,a.nickName,b.shortname as companyNameJC, "
					+ " a.phone,a.wechat,a.email,a.areacode areacode,a.areaname areaname,a.longitude,a.latitude, "
					+ " a.companyId,b.companyName,b.companyType,f.`name` companyTypeName, "
					+ " b.industry,c.`name` industryName,b.industryMemo,b.businessType,d.`name` businessTypeName, "
					+ " b.brand,b.address,b.legalPerson,b.uniformSocialCreditCode "
					+ " from  t_user a "
					+ " left join t_company b on a.companyId=b.companyId "
					+ " left join t_param c on b.industry=c.`value` and c.param_type='82' "
					+ " left join t_param d on b.businessType=d.`value` and d.param_type='81' "
					+ " left join t_param f on b.companyType=f.`value` and f.param_type='95' "
					+ " where a.sfsc=0 and a.id=").append(req.getUinfo().getId());
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString());
		    Map<String, Object> map = list.get(0);

		    //用户头像
		    if(!StringUtils.isEmpty((String) map.get("pic"))) {
		    	String picRealUrl = OssUtil.getImgSignURL((String) map.get("pic"), 1);
		    	map.put("picRealUrl", picRealUrl);
		    }
		    //营业执照
		    if(!StringUtils.isEmpty((String) map.get("businessLicense"))) {
		    	String businessLicenseRealUrl = OssUtil.getImgSignURL((String) map.get("businessLicense"), 1);
		    	map.put("businessLicenseRealUrl", businessLicenseRealUrl);
		    }

			//业务方向
			String businessType1=(String) map.get("businessType");
			if(businessType1 !=null &&! "".equals(businessType1)){
				String[] businessType=businessType1.split(";");
				map.put("businessType", businessType);
			}

			//门店logo
			if(!StringUtils.isEmpty((String) map.get("mer_logo"))) {
				ArrayList<Object> imgUrlMap=new ArrayList<Object>();
				String mer_img=(String) map.get("mer_logo");
				String[] imgArr=mer_img.split(";");
				if(imgArr.length>0){
					for(int i=0;i<imgArr.length;i++){
						String imgName=imgArr[i];
						String imgUrl=OssUtil.getImgSignURL(imgName,0);
						Map<String, String> imgMap=new HashMap<String,String>();
						imgMap.put("name", imgName);
						imgMap.put("url", imgUrl);
						imgUrlMap.add(i, imgMap);
					}
				}
				map.put("mer_logoUrl", imgUrlMap);
			}else {
				ArrayList<Object> imgUrlMap=new ArrayList<Object>();
				map.put("mer_logoUrl", imgUrlMap);
			}

			//店铺图片
			if(!StringUtils.isEmpty((String) map.get("mer_img"))) {
				ArrayList<Object> imgUrlMap=new ArrayList<Object>();
				String mer_img=(String) map.get("mer_img");
				String[] imgArr=mer_img.split(";");
				if(imgArr.length>0){
					for(int i=0;i<imgArr.length;i++){
						String imgName=imgArr[i];
						String imgUrl=OssUtil.getImgSignURL(imgName,0);
						Map<String, String> imgMap=new HashMap<String,String>();
						imgMap.put("name", imgName);
						imgMap.put("url", imgUrl);
						imgUrlMap.add(i, imgMap);
					}
				}
				map.put("mer_imgUrl", imgUrlMap);
			}else {
				ArrayList<Object> imgUrlMap=new ArrayList<Object>();
				map.put("mer_imgUrl", imgUrlMap);
			}

			res.setData(map);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	@SuppressWarnings("unchecked")
	public PageData updateUser(DataReq req) {
		PageData res = new PageData();
		HashMap<String, Object> param = req.getReqData();
		DataReq req1 = new DataReq();
		res.setRetCode(9);
		res.setRetMessage("保存信息失败");
		newg_status = begin_newg();// 开启事物
		PageData sqlInfo = null;
		try{
			//修改公司信息
			HashMap<String, Object> companyParam = new HashMap<String, Object>();
			companyParam.put("companyId", param.get("companyId"));
			companyParam.put("companyName", param.get("companyName"));
			companyParam.put("companyType", param.get("companyType"));
			companyParam.put("industry", param.get("industry"));
			companyParam.put("industryMemo", param.get("industryMemo"));
			companyParam.put("businessType", param.get("businessType"));
			companyParam.put("brand", param.get("brand"));
			companyParam.put("areacode", param.get("areacode"));
			companyParam.put("address", param.get("address"));
			companyParam.put("legalPerson", param.get("legalPerson"));
			companyParam.put("contactNumber", param.get("contactNumber"));
			companyParam.put("longitude", param.get("longitude"));
			companyParam.put("latitude", param.get("latitude"));
			companyParam.put("uniformSocialCreditCode", param.get("uniformSocialCreditCode"));

			companyParam.put("mer_id", param.get("mer_id"));
			if(param.get("mer_id") ==null || "".equals(param.get("mer_id"))){
				companyParam.put("isnewshop","0");
			}else{//如果门店id不为空，isnewshop写为1
				companyParam.put("isnewshop","1");
			}
			companyParam.put("mer_name", param.get("mer_name"));
			companyParam.put("mer_scale", param.get("mer_scale"));
			companyParam.put("mer_business", param.get("mer_business"));
			companyParam.put("mer_introduction", param.get("mer_introduction"));
			companyParam.put("mer_timeStart", param.get("mer_timeStart"));
			companyParam.put("mer_timeEnd", param.get("mer_timeEnd"));
			companyParam.put("mer_img", param.get("mer_img"));
			companyParam.put("mer_isShowPrize", param.get("mer_isShowPrize"));
			companyParam.put("mer_logo", param.get("mer_logo"));

			companyParam.put("tableName", "t_company");
//			res = saveCompany(companyReq);
			try {
				String companyId=(String)companyParam.get("companyId");
				if (companyId ==null || "".equals(companyId)){
					companyParam.put("is_delete", "0");
					companyParam.put("sort", "0");
					//查询父ID下的所有id,排序，取最后一个加+1
					companyId = getNextId("0000");
					//companyId = UUID.randomUUID().toString();
					companyParam.put("companyId",companyId) ;
					req1.setReqData(companyParam);
					sqlInfo = getInsertSQL(req1);
				}else{
					companyParam.put("keys", "companyId") ;
					req1.setReqData(companyParam);
					sqlInfo = getUpdateSQL(req1);
				}
				if (sqlInfo.getRetCode() != 0) {
					rollback_newg(newg_status);
					return sqlInfo;
				}
				final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
				final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
				this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

//				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
				res.setKey(companyParam.get("companyId").toString());
			} catch (Exception e) {
				e.printStackTrace();
				rollback_newg(newg_status);
			} finally {

			}

			//修改用户信息
			HashMap<String, Object> userParam = new HashMap<String, Object>();
			userParam.put("uuid", param.get("uuid"));
			userParam.put("nickName", param.get("nickName"));
			userParam.put("pic", param.get("pic"));
			userParam.put("areacode", param.get("areacode"));
			userParam.put("areaname", param.get("areaname"));
			userParam.put("companyId", res.getKey());
			userParam.put("phone", param.get("phone"));
			userParam.put("wechat", param.get("wechat"));
			userParam.put("email", param.get("email"));
			userParam.put("longitude", param.get("longitude"));
			userParam.put("latitude", param.get("latitude"));
			userParam.put("identity", param.get("identity"));

			userParam.put("tableName", "t_user");
//			res = saveUser(userReq);
			try {
				String uuid=(String)userParam.get("uuid");
				if (uuid ==null || "".equals(uuid)){
					userParam.put("password",EncryptedUtil.generate(userParam.get("password").toString(),param.get("username").toString()));//加密密码
					req1.setReqData(userParam);
					sqlInfo = getInsertSQL(req1);
				}else{
					userParam.put("keys", "uuid") ;
					if(null==userParam.get("password") || userParam.get("password").equals("")){//密码为空说明没有修改密码
						userParam.remove("password");
					}else{
						userParam.put("password",EncryptedUtil.generate(userParam.get("password").toString(),param.get("username").toString()));//加密密码
					}
					req1.setReqData(userParam);
					sqlInfo = getUpdateSQL(req1);
				}
				if (sqlInfo.getRetCode() != 0) {
					rollback_newg(newg_status);
					return sqlInfo;
				}
				final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
				final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
				this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

				commit_newg(newg_status);
				res.setKey((String)userParam.get("uuid")) ;
				res.setRetCode(0);
				res.setRetMessage("OK");
			} catch (Exception e) {
				e.printStackTrace();
				rollback_newg(newg_status);
			} finally {

			}
		}catch(Exception e){
			e.printStackTrace();
		}

		return res;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public PageData checkCret(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询失败");
        StringBuffer sb = new StringBuffer();
        try {
            sb.append("select status,message,if(status=0,'未进行验证',if(status=1,'验证成功','验证失败')) msg from baidu_realface where business_id=").append(req.getUinfo().getId()).append(" order by createTime desc limit 1 ");
            List<Map<String,Object>> s = this.getJdbcTemplate_newg().queryForList(sb.toString());
            Map<String,Object> map = new HashMap();
            if(s!=null && s.size()>0) {
            	map.put("status", s.get(0).get("status"));
                map.put("msg", s.get(0).get("msg"));
                map.put("memo", s.get(0).get("message"));
            }else {
            	map.put("status", 0);
                map.put("msg", "未进行验证");
            }

            res.setData(map);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

        }
        return res;
    }

	public PageData saveBusinessLicense(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("营业执照保存失败");
        newg_status = begin_newg();// 开启事物
        StringBuffer sb = new StringBuffer();
        try {
        	//判断统一社会信用代码是否存在
        	sb.delete(0, sb.length());
        	sb.append("select count(1) cnt from t_company where sfsc = 0 and uniformSocialCreditCode='").append(param.get("uniformSocialCreditCode")).append("' ");
        	int i = this.getJdbcTemplate_newg().queryForObject(sb.toString(), Integer.class);
        	if(i>0) {
        		res.setRetCode(9);
                res.setRetMessage("该营业执照已被认证过，请勿重复使用!");
                rollback_newg(newg_status);
                return res;
        	}
        	sb.delete(0, sb.length());
        	//保存营业执照、公司类型、统一社会信用代码、公司名称、认证用户ID
            sb.append("update t_company set businessLicense='").append(param.get("businessLicense"))
            .append("',companyType='").append(param.get("companyType")).append("' ")
            .append(",uniformSocialCreditCode='").append(param.get("uniformSocialCreditCode")).append("' ")
            .append(",companyName='").append(param.get("companyName")).append("' ")
            .append(",cretUserId=").append(req.getUinfo().getId())
			.append(",shortname='").append(param.get("companyNameJC")).append("' ")
			.append(",isCert='1' ")
            .append(" where companyId='").append(param.get("companyId")).append("' ");
            this.getJdbcTemplate_newg().update(sb.toString());
            commit_newg(newg_status);
//            String name = "";
            String role_id = "";
			//生成公司管理员角色
            if(!StringUtils.isEmpty((String) param.get("companyType"))) {
//            	if("01".equals(param.get("companyType"))) {
//            		name = "generate_manufacturer";
//            		sb.delete(0, sb.length());
//        			sb.append(" select GROUP_CONCAT(b.model_id) model "
//        					+ " from t_role a "
//        					+ " left join t_role_model b on a.role_id=b.role_id"
//        					+ " where a.role_id='" + param.get("companyType") + "'");
//            	}else if("02".equals(param.get("companyType"))) {
//            		name = "generate_agent";
//            		sb.delete(0, sb.length());
//        			sb.append(" select GROUP_CONCAT(b.model_id) model "
//        					+ " from t_role a "
//        					+ " left join t_role_model b on a.role_id=b.role_id"
//        					+ " where a.role_id='" + param.get("companyType") + "'");
//            	}else {
//            		name="generate_agent";
//            		sb.delete(0, sb.length());
//        			sb.append(" select GROUP_CONCAT(b.model_id) model "
//        					+ " from t_role a "
//        					+ " left join t_role_model b on a.role_id=b.role_id"
//        					+ " where a.role_id='" + param.get("companyType") + "'");
//            	}

            	sb.delete(0, sb.length());
    			sb.append(" select GROUP_CONCAT(b.model_id) model "
    					+ " from t_role a "
    					+ " left join t_role_model b on a.role_id=b.role_id"
    					+ " where a.role_id='" + param.get("companyType") + "'");
            	List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] {});
            	HashMap<String, Object> roleParam = new HashMap<String,Object>();
            	roleParam.put("companyId", req.getUinfo().getCompanyId());
            	roleParam.put("name", "企业管理员");
            	roleParam.put("isAuthorRole", "1");
            	roleParam.put("model_id", list.get(0).get("model"));
            	DataReq roleReq = new DataReq();
            	roleReq.setReqData(roleParam);
            	roleReq.setUinfo(req.getUinfo());
            	PageData res1 = saveRoleAndModel(roleReq);
            	role_id = res1.getKey();
            }
            newg_status = begin_newg();// 开启事物
        	//设置用户为已认证
        	StringBuffer sb1 = new StringBuffer();
        	sb1.append("update t_user set isCert=1 ,userType='04' "); //userType 04 管理
        	if(!StringUtils.isEmpty(role_id)) {
        		sb1.append(",role_id='").append(role_id).append("' ");
        	}
        	sb1.append(" where id=").append(req.getUinfo().getId());
        	this.getJdbcTemplate_newg().update(sb1.toString());

        	commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("OK");

        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {

        }
        return res;
    }

	public PageData saveUser(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("用户信息保存失败");
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_user");
		String pwd = (String) param.get("password");
		PageData sqlInfo = null;
		// 记录初始密码
		String password = String.valueOf(param.get("password"));
		Integer pushType = PushUserInfoVO.PUSH_TYPE_INSERT;
		try {
			// 插入一个判断, 一个部门仅允许有一个项目施工负责人
			// if (ObjectUtil.isNotNull(param.get("is_foreman")) && ObjectUtil.isNotNull(param.get("departmentId")) && "1".equals(String.valueOf(param.get("is_foreman")))) {
			// 	Boolean haveForeman = systemUserService.checkHaveForeman(String.valueOf(param.get("departmentId")));
			// 	if (haveForeman) {
			// 		res.setRetCode(9);
			// 		res.setRetMessage("当前部门已存在项目施工负责人");
			// 		return res;
			// 	}
			// }

			String expirationdate=(String) param.get("expirationdate");
			if("".equals(expirationdate)){param.put("expirationdate", null);}
			String uuid=(String)param.get("uuid");

			//判断用户名是否存在
			StringBuffer sb1 = new StringBuffer();
			sb1.append("select count(1) cnt from t_user where sfsc=0 and username='").append(param.get("username")).append("' ");
			sb1.append(" and companyId = '").append(param.get("companyId")).append("'");
			if (StrUtil.isNotEmpty(uuid)) {
				sb1.append(" and uuid != '").append(uuid).append("'");
			}
			int result = this.getJdbcTemplate_newg().queryForObject(sb1.toString(), Integer.class);
			if(result>0) {
				res.setRetCode(9);
				res.setRetMessage("用户名已存在");
				rollback_newg(newg_status);
				return res;
			}

			if (uuid ==null || "".equals(uuid)){
				param.put("isCret", "1");
				if(StringUtils.isEmpty((String) param.get("password"))) {
					res.setRetCode(9);
					res.setRetMessage("密码不能为空!");
					rollback_newg(newg_status);
					return res;
				}
				param.put("password",EncryptedUtil.generate(param.get("password").toString(),param.get("username").toString()));//加密密码
				sqlInfo = getInsertSQL(req);
			}else{
				pushType = PushUserInfoVO.PUSH_TYPE_UPDATE;
				param.put("keys", "uuid") ;
				if(null==param.get("password") || param.get("password").equals("")){//密码为空说明没有修改密码
					param.remove("password");
				}else{
					param.put("password",EncryptedUtil.generate(param.get("password").toString(),param.get("username").toString()));//加密密码
				}
				sqlInfo = getUpdateSQL(req);
			}

			if (sqlInfo.getRetCode() != 0) {
				rollback_newg(newg_status);
				return sqlInfo;
			}
			final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
			this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

			//删除项目对应关系
			StringBuffer sb = new StringBuffer();
			sb.delete(0, sb.length());
			sb.append(" update t_user_project set sfsc='1' ");
			sb.append(" WHERE userId = ? ");
			this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{param.get("uuid")});
			//添加项目对应关系
			String projectId=(String) param.get("projectId");
			if(projectId !=null &&! "".equals(projectId)){
				String[] subList=projectId.split(";");
				for(int i=0;i<subList.length;i++){
					sb.delete(0, sb.length());
					sb.append(" INSERT INTO t_user_project (userId,projectId) VALUES (?,?)");
					this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{param.get("uuid"),subList[i]});
				}
			}

			sb1.delete(0, sb1.length());
			sb1.append("SELECT nes_companyId,companyType, cc_companyId from t_company where companyId='").append(param.get("companyId")).append("' ");
			List<Map<String, Object>> companyList = this.getJdbcTemplate_newg().queryForList(sb1.toString());
			if (CollUtil.isEmpty(companyList)) {
				res.setRetCode(9);
				res.setRetMessage("未查询到公司信息");
				rollback_newg(newg_status);
				return res;
			}

			// 当公司为厂商时，进行同步用户
			String companyType = (String) companyList.get(0).get("companyType");
			String companyId = (String) companyList.get(0).get("nes_companyId");
			String callCenterCompanyId = (String) companyList.get(0).get("cc_companyId");
			if (companyType.equals("01") && StrUtil.isEmpty(companyId)) {
				res.setRetCode(9);
				res.setRetMessage("当前公司未绑定厂商账号");
				rollback_newg(newg_status);
				return res;
			}

			if (StrUtil.isNotEmpty(companyId)) {
				try {//加try是再创建用户出错的情况下，不影响用户创建
					// 调用厂商平台创建用户角色接口，同步创建用户
					// 以下4中类型用户需要同步
					HashMap<String, String> userTypeMap = new HashMap<>();
					userTypeMap.put("01", "维修");
					userTypeMap.put("02", "安装");
					userTypeMap.put("04", "管理");
					userTypeMap.put("08", "安装维修");
					String userType = (String) req.getReqData().get("userType");
					if (uuid == null || "".equals(uuid)) {
						req.getReqData().put("executeFlag", "0");
					} else {
						req.getReqData().put("executeFlag", "1");
					}
					// 用户属于上述四类，进行同步用户
					if (userTypeMap.containsKey(userType)) {
						log.debug("同步创建厂商平台用户, 公司:{}, 用户类型:{}",companyId, userTypeMap.get(userType));
						req.getReqData().put("password", pwd);
						req.getReqData().put("companyId", companyId);
						String url = CustomConfigurationProperties.getMtpfUrl() + "/nes-mtpf/user/createUsersAndRoles";
						String s = HttpUtils.sendJSONPost(url, JSONObject.toJSONString(req.getReqData()));
						JSONObject syncUserResult = JSONObject.parseObject(s);
						if (!syncUserResult.get("retCode").equals(0)) {
							log.error("同步创建厂商平台用户失败：" + syncUserResult.toJSONString());
						} else {
							log.debug("同步创建厂商平台用户成功!");
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
				}

			}

			// 客服中心同步创建用户
			if (StrUtil.isNotEmpty(callCenterCompanyId)) {
				// 准备参数
				PushUserInfoVO pushUserInfoVO = new PushUserInfoVO();
				pushUserInfoVO.setCompanyId(companyId);
				pushUserInfoVO.setUuid((String)param.get("uuid"));
				pushUserInfoVO.setCallCenterCompanyId(callCenterCompanyId);
				pushUserInfoVO.setPassword(password);
				pushUserInfoVO.setPushType(pushType);
				// 推送用户数据
				systemUserService.pushUserInfo(pushUserInfoVO);
			}

			commit_newg(newg_status);
			res.setKey((String)param.get("uuid")) ;
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {

		}
		return res;
	}

	@SuppressWarnings("unchecked")
	public PageData delUser(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("删除信息失败");
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_user");
		param.put("keys", "uuid") ;
		try {
			//修改角色sfsc是否删除状态
			param.put("sfsc", "1");
			param.put("mfepuser", "");

			PageData sqlInfo = getUpdateSQL(req);
			if (sqlInfo.getRetCode() != 0) {
				rollback_newg(newg_status);
				return sqlInfo;
			}
			StringBuffer sb_field = null ;
			List<String> listData =  null ;

			sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			listData = (List<String>) sqlInfo.getData().get("listData");
			this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

			commit_newg(newg_status);
			res.setKey((String)param.get("uuid")) ;
			res.setRetCode(0);
			res.setRetMessage("OK");

			// 调用接口同步易修、长效管护用户信息
			// 准备参数
			PushUserInfoVO pushUserInfoVO = new PushUserInfoVO();
			pushUserInfoVO.setUuid((String)param.get("uuid"));
			pushUserInfoVO.setPushType(PushUserInfoVO.PUSH_TYPE_DELETE);
			pushUserInfoVO.setCompanyId(req.getUinfo().getCompanyId());
			pushUserInfoVO.setCallCenterCompanyId(req.getUinfo().getCallCenterCompanyId());
			// 推送用户数据
			systemUserService.pushUserInfo(pushUserInfoVO);
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {

		}
		return res;
	}

	@SuppressWarnings("unchecked")
	public PageData saveRoleAndModel(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("修改角色权限失败");
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_role");
		PageData sqlInfo =null;
		String role_id=(String)param.get("role_id");
		try {
			if (role_id ==null || "".equals(role_id)){
				role_id = UUID.randomUUID().toString();
				param.put("role_id", role_id) ;
				sqlInfo = getInsertSQL(req);
			}else{
				param.put("keys", "role_id") ;
				sqlInfo = getUpdateSQL(req);
			}
			if (sqlInfo.getRetCode() != 0) {
				rollback_newg(newg_status);
				return sqlInfo;
			}
			StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			List<String> listData = (List<String>) sqlInfo.getData().get("listData");
			this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

			//根据角色ID删除此角色下所有权限
			if(!StringUtils.isEmpty(role_id)){
				StringBuffer sb = new StringBuffer();
				sb.append("delete from t_role_model where role_id='").append(req.getReqData().get("role_id")).append("' ");
				this.getJdbcTemplate_newg().update(sb.toString());
			}

			//批量添加权限
			String model_id=(String)param.get("model_id");
			String isWrite=(String)param.get("isWrite");
			param.put("tableName", "t_role_model");
			if (model_id != null) {//单个权限
				param.put("sfsc", "0");
				String[] model_idList = model_id.split(",");
				String[] isWriteList = isWrite.split(",");
				for (int i = 0; i < model_idList.length; i++) {
					param.put("model_id", model_idList[i]);
					param.put("isWrite", isWriteList[i]);
					//param.put("isWrite", 1);
					sqlInfo = getInsertSQL(req);
					if (sqlInfo.getRetCode() != 0) {
						rollback_newg(newg_status);
						return sqlInfo;
					}
					sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
					listData = (List<String>) sqlInfo.getData().get("listData");
					this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());
				}
			}

			commit_newg(newg_status);
			newg_status = begin_newg();
			//如果role_id=01 或 role_id=02
			if("01".equals(role_id) || "02".equals(role_id)) {
				//查询所有企业管理员角色
				StringBuffer sb = new StringBuffer();
				sb.append(" select a.role_id,b.id"
						+ " from t_role a"
						+ " inner join t_user b on a.role_id=b.role_id and b.sfsc=0 "
						+ " left join t_company c on b.companyId=c.companyId"
						+ " where isAuthorRole='1' and c.companyType='" + role_id + "' and b.id !=458 "); //and b.id !=458 排除Z赵永远厂商用户
				List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString());
				//遍历将角色修改为最新的
				for(Map<String,Object> li : list) {
					String id = (String) li.get("role_id");
					//根据角色ID删除此角色下所有权限
					if(!StringUtils.isEmpty(id)){
						sb.delete(0, sb.length());
						sb.append("delete from t_role_model where role_id='").append(id).append("' ");
						this.getJdbcTemplate_newg().update(sb.toString());
					}
					//批量添加权限
					String model_id1=model_id;
					param.put("tableName", "t_role_model");
					param.put("role_id", id);
					if (model_id1 != null) {//单个权限
						param.put("sfsc", "0");
						String[] model_idList = model_id1.split(",");
						for (int i = 0; i < model_idList.length; i++) {
							param.put("model_id", model_idList[i]);
							sqlInfo = getInsertSQL(req);
							if (sqlInfo.getRetCode() != 0) {
								rollback_newg(newg_status);
								return sqlInfo;
							}
							sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
							listData = (List<String>) sqlInfo.getData().get("listData");
							this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());
						}
					}
				}
			}
			commit_newg(newg_status);
			res.setKey(role_id) ;
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {

		}
		return res;
	}

	@SuppressWarnings("unchecked")
	public PageData delRoleAndModel(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("删除角色权限失败");
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_role");
		param.put("role_id",req.getReqData().get("role_id"));
		param.put("keys", "role_id") ;
		//设置用户权限分两步，1.修改角色名称，2.根据角色ID删除此角色下所有权限，然后添加权限
		try {
			//修改角色sfsc是否删除状态
			param.put("sfsc", "1");
			PageData sqlInfo = getUpdateSQL(req);
			if (sqlInfo.getRetCode() != 0) {
				rollback_newg(newg_status);
				return sqlInfo;
			}
			StringBuffer sb_field = null ;
			List<String> listData =  null ;

			sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			listData = (List<String>) sqlInfo.getData().get("listData");
			this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

			//根据角色ID删除此角色下所有权限
			param.remove("model_id");
			param.put("tableName", "t_role_model");
			sqlInfo = getUpdateSQL(req);
			if (sqlInfo.getRetCode() != 0) {
				rollback_newg(newg_status);
				return sqlInfo;
			}

			sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			listData = (List<String>) sqlInfo.getData().get("listData");
			this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

			commit_newg(newg_status);
			res.setKey((String)param.get("uuid")) ;
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {

		}
		return res;
	}

	public PageData getRoles(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取用户角色信息失败");
		try {
			StringBuffer sb = new StringBuffer();
			StringBuffer sbfrom = new StringBuffer() ;
			sb.append(" select * from ( ");
			sb.append(" SELECT a.isAuthorRole,a.companyId,d.companyName,a.role_id,a.name,a.UUID,a.createUid,DATE_FORMAT(a.createTime,'%Y-%m-%d %H-%m-%s') createTime,a.modifyUid,DATE_FORMAT(a.modifyTime, '%Y-%m-%d %H-%m-%s') modifyTime,SUM(IF(a.sfsc=0 AND b.sfsc=0 AND c.sfsc=0 AND c.is_show=1,1,0)) pcqxs ") ;
			sb.append(" from t_role a ");
			sb.append(" LEFT JOIN t_role_model b ON a.role_id=b.role_id AND b.sfsc=0 AND a.sfsc=0 ");
			sb.append(" LEFT JOIN t_model c ON b.model_id=c.model_id AND c.is_show=1 AND c.sfsc=0 ");
			sb.append(" LEFT JOIN t_company d ON a.companyId=d.companyId");
			sb.append(" WHERE 1=1  AND a.sfsc = 0  ") ;
			if (!StringUtils.isEmpty(req.getUinfo().getCompanyId())&&!"0000".equals(req.getUinfo().getCompanyId())){
				sb.append(" and a.companyId like '").append(req.getUinfo().getCompanyId()).append("%' ") ;
				//sb.append(" and companyId in ( ").append(getAllNextCompanyId(req.getUinfo().getCompanyId())).append(" ) ");
			}
			if (!StringUtils.isEmpty((String)param.get("name"))){
				sb.append(" and a.name like '%").append(param.get("name")).append("%' ") ;
			}
			if (!StringUtils.isEmpty((String)param.get("companyId"))){
				sb.append(" and a.companyId = '").append(param.get("companyId")).append("' ") ;
			}
			sb.append(" group by a.role_id ) a");
			//分页
			sb = DBUtils.sql_check(sb) ;
			sbfrom.append(sb.toString());
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,new Object[] {});
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});

			for(int i=0;i<list.size();i++){
				param.put("role_id", list.get(i).get("role_id"));
				List<Map<String, Object>> listAll=(List<Map<String, Object>>) getModels(req).getPage();//全部模块
				list.get(i).put("submenu", listAll);
			}

			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");

		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getListCheck(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取失败");
		StringBuffer sb = new StringBuffer();
		String role_id = (String) param.get("role_id");
		try {
			sb.append("SELECT * from ( ");
			sb.append("SELECT 1 isCheck,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,3 level,b.isWrite from t_model a INNER JOIN t_role_model b  on a.model_id=b.model_id and length(b.model_id)=6 and b.role_id=? and b.sfsc='0' ");
			sb.append("union ");
			sb.append("SELECT 1 isCheck,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,3 level,b.isWrite from t_model a INNER JOIN t_role_model b  on a.model_id=b.model_id and length( b.model_id )= 4 and b.role_id=? and b.sfsc='0' ");
			sb.append("union ");
			sb.append("SELECT 1 isCheck,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,2 level,b.isWrite from t_model a INNER JOIN t_role_model b  on a.model_id=SUBSTR(b.model_id,1,4) and length(b.model_id)<>2 and b.role_id=? and b.sfsc='0' ");
			sb.append(" GROUP BY a.model_id ");
			sb.append("union ");
			sb.append("SELECT 1 isCheck,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,1 level,b.isWrite from t_model a INNER JOIN t_role_model b  on a.model_id=SUBSTR(b.model_id,1,2) and b.role_id=? and b.sfsc='0' ");
			sb.append(" GROUP BY a.model_id ");
			sb.append(") m ");
			sb.append("ORDER BY m.model_id,m.p_model_id,m.level  ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] { role_id, role_id, role_id , role_id});
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getModels(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取功能模块信息失败");
		try {
			StringBuffer sb = new StringBuffer();
			String companyType = req.getUinfo().getCompanyType() ;
			String role_id = req.getUinfo().getRole_id();
			sb.append("	SELECT * from ( ");
			sb.append("	SELECT IF(a.type = '01','(厂家)',IF(a.type = '02','(代理商)','')) typeName,0 isCheck,b.isWrite,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,a.sort,3 level from t_model a INNER JOIN t_role_model b  on a.model_id=b.model_id and  length( b.model_id)=6 and b.role_id=? and b.sfsc='0' ");
			sb.append("	where (a.type in ('00',?) or ?='00') ");
			sb.append("	union ");
			sb.append("	SELECT IF(a.type = '01','(厂家)',IF(a.type = '02','(代理商)','')) typeName,0 isCheck,b.isWrite,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,a.sort,3 level from t_model a INNER JOIN t_role_model b ON a.model_id = b.model_id AND length( b.model_id )= 4 AND b.role_id =? AND b.sfsc = '0' ");
			sb.append("	WHERE ( a.type IN ( '00',?) OR ?= '00') ");
			sb.append("	union ");
			sb.append("	SELECT IF(a.type = '01','(厂家)',IF(a.type = '02','(代理商)','')) typeName,0 isCheck,b.isWrite,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,a.sort,2 level from t_model a INNER JOIN t_role_model b  on a.model_id=SUBSTR(b.model_id,1,4) and length(b.model_id)<>2 and b.role_id=? and b.sfsc='0' ");
			sb.append("	where (a.type in ('00',?) or ?='00') GROUP BY a.model_id ");
			sb.append("	union ");
			sb.append("	SELECT IF(a.type = '01','(厂家)',IF(a.type = '02','(代理商)','')) typeName,0 isCheck,b.isWrite,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,a.sort,1 level from t_model a INNER JOIN t_role_model b  on a.model_id=SUBSTR(b.model_id,1,2) and b.role_id=? and b.sfsc='0' ");
			sb.append("	where (a.type in ('00',?) or ?='00') GROUP BY a.model_id ");
			sb.append("	) m ");
			if (!StringUtils.isEmpty((String)param.get("model_id"))){
				sb.append(" where m.model_id = '").append(param.get("model_id")).append("' ");
			}
			sb.append(" ORDER BY m.sort,SUBSTR(m.model_id,2),m.model_id,p_model_id,m.level ");
			// System.out.println(sb);
			// System.out.println(role_id);
			// System.out.println(companyType);
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] { role_id, companyType,companyType,role_id, companyType,companyType,role_id ,companyType,companyType, role_id,companyType,companyType});
			List<Map<String, Object>> listCheck = (List<Map<String, Object>>) getListCheck(req).getPage();//角色选中模块
			for (int j=0;j<list.size();j++) {
				for (int k=0;k<listCheck.size();k++) {
					if(listCheck.get(k).get("model_id").equals(list.get(j).get("model_id"))){
						list.get(j).put("isCheck", "1");
					}
				}
			}
			// 转换list
			List<Map<String, Object>> list1 = new ArrayList<Map<String, Object>>();
			List<Map<String, Object>> list2 = new ArrayList<Map<String, Object>>();
			List<Map<String, Object>> list3 = null;
			Map<String, Object> Menu1 = null;
			for (Map<String, Object> data1 : list) {
				Long level1= (Long) data1.get("level");
				if(level1 == 1) {
					Map<String, Object> menu2 = null;
					for (Map<String, Object> data2 : list) {
						Long level2 = (Long) data2.get("level");
						if (level2 == 2) {
							if(data2.get("g_model_id").equals(data1.get("g_model_id"))){
								menu2 = data2;
								list2.add(menu2);
								list3 = new ArrayList<Map<String, Object>>();
								menu2.put("submenu", list3);
							}
						}
						if(level2 == 3) {
							if(data2.get("g_model_id").equals(data1.get("g_model_id"))){
								list3.add(data2);
							}
						}
					}
					Menu1 = data1;
					Menu1.put("submenu", list2);
					list1.add(Menu1);
					list2 = new ArrayList<Map<String, Object>>();
				}
			}
			res.setPage(list1);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getRoles_new(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取用户角色信息失败");
		try {
			StringBuffer sb = new StringBuffer();
			StringBuffer sbfrom = new StringBuffer() ;
			sb.append(" select * from ( ");
			sb.append(" SELECT a.isAuthorRole,a.companyId,d.companyName,a.role_id,a.name,a.UUID,a.createUid,DATE_FORMAT(a.createTime,'%Y-%m-%d %H-%m-%s') createTime,a.modifyUid,DATE_FORMAT(a.modifyTime, '%Y-%m-%d %H-%m-%s') modifyTime,SUM(IF(a.sfsc=0 AND b.sfsc=0 AND c.sfsc=0 AND c.is_show=1,1,0)) pcqxs ") ;
			sb.append(" from t_role a ");
			sb.append(" LEFT JOIN t_role_model b ON a.role_id=b.role_id AND b.sfsc=0 AND a.sfsc=0 ");
			sb.append(" LEFT JOIN t_model c ON b.model_id=c.model_id AND c.is_show=1 AND c.sfsc=0 ");
			sb.append(" LEFT JOIN t_company d ON a.companyId=d.companyId");
			sb.append(" WHERE 1=1  AND a.sfsc = 0  ") ;
			if (!StringUtils.isEmpty(req.getUinfo().getCompanyId())&&!"0000".equals(req.getUinfo().getCompanyId())){
				sb.append(" and a.companyId like '").append(req.getUinfo().getCompanyId()).append("%' ") ;
				//sb.append(" and companyId in ( ").append(getAllNextCompanyId(req.getUinfo().getCompanyId())).append(" ) ");
			}
			if (!StringUtils.isEmpty((String)param.get("name"))){
				sb.append(" and a.name like '%").append(param.get("name")).append("%' ") ;
			}
			if (!StringUtils.isEmpty((String)param.get("companyId"))){
				sb.append(" and a.companyId = '").append(param.get("companyId")).append("' ") ;
			}
			sb.append(" group by a.role_id ) a");
			//分页
			sb = DBUtils.sql_check(sb) ;
			sbfrom.append(sb.toString());
			int idx = sbfrom.indexOf(" from ");
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(), req.getPageid(), req.getPagesize(), res,new Object[] {});
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});

			for(int i=0;i<list.size();i++){
				param.put("role_id", list.get(i).get("role_id"));
				List<Map<String, Object>> listAll=(List<Map<String, Object>>) getModels_new(req).getPage();//全部模块
				list.get(i).put("submenu", listAll);
			}

			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");

		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getListCheck_new(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取失败");
		StringBuffer sb = new StringBuffer();
		String role_id = (String) param.get("role_id");
		try {
			sb.append("SELECT * from ( ");
			sb.append("SELECT 1 isCheck,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,3 level,isWrite  from t_model a INNER JOIN t_role_model b  on a.model_id=b.model_id and length(b.model_id)=6 and b.role_id=? and b.sfsc='0' ");
			sb.append("union ");
			sb.append("SELECT 1 isCheck,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,3 level,isWrite  from t_model a INNER JOIN t_role_model b  on a.model_id=b.model_id and length( b.model_id )= 4 and b.role_id=? and b.sfsc='0' ");
			sb.append("union ");
			sb.append("SELECT 1 isCheck,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,2 level,isWrite from t_model a INNER JOIN t_role_model b  on a.model_id=SUBSTR(b.model_id,1,4) and length(b.model_id)<>2 and b.role_id=? and b.sfsc='0' ");
			sb.append(" GROUP BY a.model_id ");
			sb.append("union ");
			sb.append("SELECT 1 isCheck,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,1 level,isWrite from t_model a INNER JOIN t_role_model b  on a.model_id=SUBSTR(b.model_id,1,2) and b.role_id=? and b.sfsc='0' ");
			sb.append(" GROUP BY a.model_id ");
			sb.append(") m ");
			sb.append("ORDER BY m.model_id,m.p_model_id,m.level  ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] { role_id, role_id, role_id , role_id});
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getModels_new(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取功能模块信息失败");
		try {
			StringBuffer sb = new StringBuffer();
			String companyType = req.getUinfo().getCompanyType() ;
			String role_id = req.getUinfo().getRole_id();
			sb.append("	SELECT * from ( ");
			sb.append("	SELECT IF(a.type = '01','(厂家)',IF(a.type = '02','(代理商)','')) typeName,0 isCheck,'0' isWrite,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,a.sort,3 level from t_model a INNER JOIN t_role_model b  on a.model_id=b.model_id and  length( b.model_id)=6 and b.role_id=? and b.sfsc='0' ");
			sb.append("	where (a.type in ('00',?) or ?='00') ");
			sb.append("	union ");
			sb.append("	SELECT IF(a.type = '01','(厂家)',IF(a.type = '02','(代理商)','')) typeName,0 isCheck,'0' isWrite,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,a.sort,3 level from t_model a INNER JOIN t_role_model b ON a.model_id = b.model_id AND length( b.model_id )= 4 AND b.role_id =? AND b.sfsc = '0' ");
			sb.append("	WHERE ( a.type IN ( '00',?) OR ?= '00') ");
			sb.append("	union ");
			sb.append("	SELECT IF(a.type = '01','(厂家)',IF(a.type = '02','(代理商)','')) typeName,0 isCheck,'0' isWrite,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,a.sort,2 level from t_model a INNER JOIN t_role_model b  on a.model_id=SUBSTR(b.model_id,1,4) and length(b.model_id)<>2 and b.role_id=? and b.sfsc='0' ");
			sb.append("	where (a.type in ('00',?) or ?='00') GROUP BY a.model_id ");
			sb.append("	union ");
			sb.append("	SELECT IF(a.type = '01','(厂家)',IF(a.type = '02','(代理商)','')) typeName,0 isCheck,'0' isWrite,a.model_id,a.p_model_id,a.g_model_id,a.`name`,a.url,a.iconClass,a.sort,1 level from t_model a INNER JOIN t_role_model b  on a.model_id=SUBSTR(b.model_id,1,2) and b.role_id=? and b.sfsc='0' ");
			sb.append("	where (a.type in ('00',?) or ?='00') GROUP BY a.model_id ");
			sb.append("	) m ");
			if (!StringUtils.isEmpty((String)param.get("model_id"))){
				sb.append(" where m.model_id = '").append(param.get("model_id")).append("' ");
			}
			sb.append(" ORDER BY m.sort,SUBSTR(m.model_id,2),m.model_id,p_model_id,m.level ");
			// System.out.println(sb);
			// System.out.println(role_id);
			// System.out.println(companyType);
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] { role_id, companyType,companyType,role_id, companyType,companyType,role_id ,companyType,companyType, role_id,companyType,companyType});
			List<Map<String, Object>> listCheck = (List<Map<String, Object>>) getListCheck_new(req).getPage();//角色选中模块
			for (int j=0;j<list.size();j++) {
				for (int k=0;k<listCheck.size();k++) {
					if(listCheck.get(k).get("model_id").equals(list.get(j).get("model_id"))){
						list.get(j).put("isCheck", "1");
						list.get(j).put("isWrite", listCheck.get(k).get("isWrite"));
					}
				}
			}
			// 转换list
			List<Map<String, Object>> list1 = new ArrayList<Map<String, Object>>();
			List<Map<String, Object>> list2 = new ArrayList<Map<String, Object>>();
			List<Map<String, Object>> list3 = null;
			Map<String, Object> Menu1 = null;
			for (Map<String, Object> data1 : list) {
				Long level1= (Long) data1.get("level");
				if(level1 == 1) {
					Map<String, Object> menu2 = null;
					for (Map<String, Object> data2 : list) {
						Long level2 = (Long) data2.get("level");
						if (level2 == 2) {
							if(data2.get("g_model_id").equals(data1.get("g_model_id"))){
								menu2 = data2;
								list2.add(menu2);
								list3 = new ArrayList<Map<String, Object>>();
								menu2.put("submenu", list3);
							}
						}
						if(level2 == 3) {
							if(data2.get("g_model_id").equals(data1.get("g_model_id"))){
								list3.add(data2);
							}
						}
					}
					Menu1 = data1;
					Menu1.put("submenu", list2);
					list1.add(Menu1);
					list2 = new ArrayList<Map<String, Object>>();
			   }
			}
			res.setPage(list1);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getAreaInfo(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取行政区划失败");
		try {
			String xzqhdm=(String)param.get("xzqhdm");
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT xzqhdm value,xzqhmc label from t_xzqh where sjqhdm='").append(xzqhdm).append("' ") ;
			sb = DBUtils.sql_check(sb) ;
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(), new Object[]{});
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");

		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getParam(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取参数信息失败");
		try {

			StringBuffer sb = new StringBuffer();
			StringBuffer sbfrom = new StringBuffer() ;
			sb.append("SELECT uuid,param_type,type_name, name,value,sjdm,flag ") ;
			sb.append(" from t_param  where is_delete='0' ");
			sb.append(" and (companyId='' or companyId is null or companyId=?) ");
			if (!StringUtils.isEmpty((String)param.get("type_name"))){
				sb.append(" and type_name like '%").append(param.get("type_name")).append("%' ") ;
			}
			sb = DBUtils.sql_check(sb) ;
			sbfrom.append(sb.toString()) ;
			int idx=sbfrom.indexOf(" from ") ;
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{req.getUinfo().getCompanyId()}) ;
			sb.append(" order by CAST(param_type AS SIGNED) ,value ");
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;

			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	@SuppressWarnings("unchecked")
	public PageData saveParam(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("参数信息保存失败");
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_param");
		PageData sqlInfo =null;
		String uuid=(String)param.get("uuid");
		try {
			if (uuid ==null || "".equals(uuid)){
				param.put("is_delete", "0");
				param.put("sort", "0");
				uuid = UUID.randomUUID().toString();
				sqlInfo = getInsertSQL(req);
			}else{
				param.put("keys", "uuid") ;
				sqlInfo = getUpdateSQL(req);
			}
			if (sqlInfo.getRetCode() != 0) {
				rollback_newg(newg_status);
				return sqlInfo;
			}
			final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
			this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {

		}
		return res;
	}

	public PageData delParam(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("删除参数信息失败");
		newg_status = begin_newg();// 开启事物
		try {
			String uuid=(String)param.get("uuid");
				if(!StringUtils.isEmpty(uuid) ){
					StringBuffer sb = new StringBuffer();
					sb.append("delete from t_param where uuid='").append(uuid).append("' ");
				this.getJdbcTemplate_newg().update(sb.toString());
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
			}else{
				rollback_newg(newg_status);
				return res;
			}
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {

		}
		return res;
	}

	public PageData getCompany(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取公司信息失败");
		try {

			StringBuffer sb = new StringBuffer();
			sb.append(" select * from ( ");
			sb.append(" (SELECT ifnull(b.usercount,0) as usercount,a.companyId,a.companyName as companyFullName,a.p_companyId as pid,a.p_companyName as pname," +
					" a.shortname as companyShortName, a.organization_code as organizationCode, a.uniformSocialCreditCode, a.contact," +
					" a.contactNumber, a.address as registerAddress, a.legalPerson, a.areacode, " +
					" a.companyType, 0 isDepartment ,a.nes_companyId,a.has_call_center, cc_companyId, a.gmt_create" +
					" from t_company a " +
					" LEFT JOIN (SELECT count(0) usercount,companyId FROM t_user where sfsc<>'1' GROUP BY companyId) b ON a.companyId=b.companyId " +
					" where a.sfsc<>'1' order by a.gmt_create asc) ");
			sb.append(" UNION ");
			sb.append(" (SELECT ifnull(b.usercount,0) as usercount,a.departmentId companyId,a.departmentName companyFullName,a.p_companyId as pid,a.p_companyName as pname," +
					" '' as companyShortName, '' as organizationCode, '' as uniformSocialCreditCode, '' as contact," +
					" '' as contactNumber, '' as registerAddress, '' as legalPerson, '' as areacode, " +
					" '00' companyType, 1 isDepartment ,'' nes_companyId, '' as has_call_center, ''cc_companyId, a.gmt_create" +
					" from t_department a " +
					" LEFT JOIN (SELECT count(0) usercount,departmentId FROM t_user where sfsc<>'1' GROUP BY departmentId) b ON a.departmentId=b.departmentId " +
					" where a.sfsc<>'1' order by a.gmt_create asc )");
			sb.append(" )x where 1=1 ");
			if (!StringUtils.isEmpty(req.getUinfo().getCompanyId())&&!"0000".equals(req.getUinfo().getCompanyId())){
				sb.append(" and companyId like '").append(req.getUinfo().getCompanyId()).append("%' ") ;
				//sb.append(" and companyId in ( ").append(getAllNextCompanyId(req.getUinfo().getCompanyId())).append(" ) ");
			}
			sb.append("order by companyType, gmt_create asc ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});

			if(list.size()>0){
				for(int i=0;i<list.size();i++){
					sb.delete(0, sb.length());
					String companyType=list.get(i).get("companyType").toString();
					if(!"00".equals(companyType)){//00是部门
						String companyId=list.get(i).get("companyId").toString();
						if("01".equals(companyType)){//厂商
							sb.append(" SELECT a.agent_id value FROM t_agent_facturer a ");
							sb.append(" LEFT JOIN t_company b on a.agent_id=b.companyId ");
							sb.append(" WHERE a.deleted <> '1' AND b.sfsc<>'1' AND a.facturer_id = ? ");
						} else {//代理商
							sb.append(" SELECT a.facturer_id value FROM t_agent_facturer a ");
							sb.append(" LEFT JOIN t_company b on a.facturer_id=b.companyId ");
							sb.append(" WHERE a.deleted <> '1' AND b.sfsc<>'1' AND a.agent_id = ? ");
						}
						List<Map<String, Object>> subList = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{companyId});
						String[] valueList=new String[subList.size()];
						for(int j=0;j<subList.size();j++){
							valueList[j]=subList.get(j).get("value").toString();
						}
						list.get(i).put("subList", valueList);
					}
				}
			}

			// 数据处理
			List<Map<String, Object>> allList = new ArrayList<Map<String, Object>>();
			List<Map<String, Object>> pList = new ArrayList<Map<String, Object>>();// eTree
			for (Map<String, Object> tmp : list) {
				Map<String, Object> map = new LinkedHashMap<String, Object>();
				if(tmp.get("companyId").equals(req.getUinfo().getCompanyId())){
					map.put("pId", "");
					map.put("pId_nm", "");
				}else{
					map.put("pId", tmp.get("pid"));
					map.put("pId_nm", tmp.get("pname"));
				}
				map.put("id", tmp.get("companyId"));
				map.put("label", tmp.get("companyFullName"));
				map.put("companyShortName", tmp.get("companyShortName"));
				map.put("organizationCode", tmp.get("organizationCode"));
				map.put("uniformSocialCreditCode", tmp.get("uniformSocialCreditCode"));
				map.put("contact", tmp.get("contact"));
				map.put("contactNumber", tmp.get("contactNumber"));
				map.put("registerAddress", tmp.get("registerAddress"));
				map.put("legalPerson", tmp.get("legalPerson"));
				map.put("areaCode", tmp.get("areaCode"));
				map.put("companyType", tmp.get("companyType"));
				map.put("isDepartment", tmp.get("isDepartment"));
				map.put("nes_companyId", tmp.get("nes_companyId"));
				map.put("usercount", tmp.get("usercount"));
				map.put("subList", tmp.get("subList"));
				map.put("has_call_center", tmp.get("has_call_center"));
				map.put("cc_companyId", tmp.get("cc_companyId"));
				allList.add(map);
			}
			// 获取父节点
			for (Map<String, Object> tmp : allList) {
				String cg_pig = tmp.get("pId").toString();
				if (StringUtil.isBlank(cg_pig)) {
					// 组装子节点，开始循环去获取子节点
					tmp.put("children", companyChild(tmp.get("id").toString(), allList));
					pList.add(tmp);
				}
			}

			res.setPage(pList);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public List<?> companyChild(String id, List<Map<String, Object>> data) {
		List<Object> lists = new ArrayList<Object>();
		for (Map<String, Object> map : data) {
			String cg_pig = map.get("pId").toString();
			if (cg_pig.equals(id)) {
				// 组装子节点，开始循环去获取子节点
				map.put("children", companyChild(map.get("id").toString(), data));
				lists.add(map);
			}
		}
		return lists;
	}

	@SuppressWarnings("unchecked")
	public PageData saveCompany(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("保存信息失败");
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_company");
		PageData sqlInfo = null;
		try {
			String companyId=(String)param.get("companyId");
			String companyType=(String)param.get("companyType");
			if (companyId ==null || "".equals(companyId)){
				param.put("is_delete", "0");
				param.put("sort", "0");
				//查询父ID下的所有id,排序，取最后一个加+1
				companyId = getNextId(param.get("p_companyId").toString());
				//companyId = UUID.randomUUID().toString();
				param.put("companyId",companyId) ;
				sqlInfo = getInsertSQL(req);
			}else{
				param.put("keys", "companyId") ;
				sqlInfo = getUpdateSQL(req);
			}
			if (sqlInfo.getRetCode() != 0) {
				rollback_newg(newg_status);
				return sqlInfo;
			}
			final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
			this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

			//删除厂商供应商对应关系
			StringBuffer sb = new StringBuffer();
			sb.delete(0, sb.length());
			if("01".equals(companyType)){//厂商
				sb.append(" update t_agent_facturer set deleted='1' ");
				sb.append(" WHERE facturer_id = ? ");
			}else if("02".equals(companyType)){//代理商
				sb.append(" update t_agent_facturer set deleted='1' ");
				sb.append(" WHERE agent_id = ? ");
			}
			this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{companyId});
			//添加厂商供应商对应关系
			String subString=(String) param.get("subList");
			if(subString !=null &&! "".equals(subString)){
				String[] subList=subString.split(";");
				for(int i=0;i<subList.length;i++){
					sb.delete(0, sb.length());
					sb.append(" INSERT INTO t_agent_facturer (agent_id,facturer_id) VALUES (?,?)");
					if("01".equals(companyType)){//厂商
						this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{subList[i],companyId});
					}else if("02".equals(companyType)){//代理商
						this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{companyId,subList[i]});
					}
				}
			}

			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
			res.setKey(param.get("companyId").toString());
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {

		}
		return res;
	}

	@SuppressWarnings("unchecked")
	public PageData delCompany(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("删除信息失败");
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_company");
		param.put("keys", "companyId") ;
		try {
			//修改角色sfsc是否删除状态
			param.put("sfsc", "1");
			PageData sqlInfo = getUpdateSQL(req);
			if (sqlInfo.getRetCode() != 0) {
				rollback_newg(newg_status);
				return sqlInfo;
			}
			StringBuffer sb_field = null ;
			List<String> listData =  null ;

			sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			listData = (List<String>) sqlInfo.getData().get("listData");
			this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

			StringBuffer sb = new StringBuffer();
			sb.append(" update t_department set sfsc='1' ");
			sb.append(" WHERE departmentId = ? ");
		    this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{req.getReqData().get("companyId")});

		    StringBuffer sb1 = new StringBuffer();
			sb1.append(" update t_user set departmentId='' ");
			sb1.append(" WHERE departmentId = ? and t_user.sfsc=0");
		    this.getJdbcTemplate_newg().update(sb1.toString(), new Object[]{req.getReqData().get("companyId")});

			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {

		}
		return res;
	}

	@SuppressWarnings("unchecked")
	public PageData saveDepartment(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("保存信息失败");
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_department");
		PageData sqlInfo = null;
		try {
			String departmentId=(String)param.get("departmentId");
			if (departmentId ==null || "".equals(departmentId)){
				param.put("is_delete", "0");
				param.put("sort", "0");
				//查询父ID下的所有id,排序，取最后一个加+1
				departmentId = getNextDepartmentId(param.get("p_companyId").toString());
				if(departmentId==null || "".equals(departmentId)){
					rollback_newg(newg_status);
					res.setRetCode(9);
					res.setRetMessage("获取部门代码失败！");
					return res;
				}
				//companyId = UUID.randomUUID().toString();
				param.put("departmentId",departmentId) ;
				sqlInfo = getInsertSQL(req);
			}else{
				param.put("keys", "departmentId") ;
				sqlInfo = getUpdateSQL(req);
			}
			if (sqlInfo.getRetCode() != 0) {
				rollback_newg(newg_status);
				return sqlInfo;
			}
			final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
			this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
			res.setKey(param.get("departmentId").toString());
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {

		}
		return res;
	}

	public PageData getRolelist(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		try {
			StringBuffer sb = new StringBuffer();
			sb.append(" SELECT a.role_id value,a.name FROM t_role a ");
			sb.append(" WHERE a.sfsc <> '1' ") ;
			//超级管理员可以分配厂家和代理商的管理角色
			if(req.getUinfo().getCompanyId().equals("0000")){
				sb.append(" and ( companyId=? or role_id='01' or role_id='02' ) ");
			}else{
				sb.append(" and companyId=? ");
			}
			List<Map<String, Object>> list =this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{req.getReqData().get("companyId")});
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getCompanyList(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		try {
			StringBuffer sb = new StringBuffer();
			sb.append(" SELECT a.companyId value,a.companyName name FROM t_company a ");
			sb.append(" WHERE a.sfsc <> '1' ") ;
			if (!StringUtils.isEmpty(req.getUinfo().getCompanyId())&&!"0000".equals(req.getUinfo().getCompanyId())){
				sb.append(" and a.companyId like '").append(req.getUinfo().getCompanyId()).append("%' ") ;
				//sb.append(" and companyId in ( ").append(getAllNextCompanyId(req.getUinfo().getCompanyId())).append(" ) ");
			}
			String companyType=req.getReqData().get("companyType").toString();
			if(!StringUtils.isEmpty(companyType)){
				if("01".equals(companyType)){//厂商
					sb.append(" and a.companyType='02' ");
				}else if("02".equals(companyType)){//代理商
					sb.append(" and a.companyType='01' ");
				}
			}

			List<Map<String, Object>> list =this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getDepartmentList(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		try {
			String companyId=req.getReqData().get("companyId").toString();
			StringBuffer sb = new StringBuffer();
			sb.append(" SELECT a.departmentId value,a.departmentName name FROM t_department a ");
			sb.append(" WHERE a.sfsc <> '1' and a.p_companyId = ? ") ;
			List<Map<String, Object>> list =this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{companyId});
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getProjectList(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		try {
			StringBuffer sb = new StringBuffer();
			String companyId = (String) req.getReqData().get("companyId");
			Object[] condition;
			if (StrUtil.isNotBlank(companyId)) {
				sb.append(" SELECT a.id value,a.project_name name FROM t_project a where a.company_id = ?");
				condition = new Object[]{companyId};
			} else {
				sb.append(" SELECT a.id value,a.project_name name FROM t_project a");
				condition = new Object[]{};
			}


			List<Map<String, Object>> list =this.getJdbcTemplate_newg().queryForList(sb.toString(), condition);
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getNesCompanyList(DataReq req) {
		PageData res = new PageData();
		List<Map<String, Object>> items = new ArrayList<Map<String, Object>>();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		try {
			StringBuffer sb = new StringBuffer();
			String isALL =  (String) req.getReqData().get("isALL") ;
			if("1".equals(isALL)) {
				sb.append(" SELECT a.manufacture_id value,a.name name,a.manufacture_id companyId,a.name companyName FROM nes_ope_prod.is_manufacture_info a   where 1=1 or '1'=?  ORDER BY a.name desc ");
			}else {
				if("02".equals(req.getUinfo().getCompanyType())) {//代理商
					sb.append(" SELECT a.manufacture_id value,a.name name,a.manufacture_id companyId,a.name companyName FROM nes_ope_prod.is_manufacture_info a where a.manufacture_id in ( ");
					sb.append(" SELECT b.nes_companyId from t_agent_facturer a INNER JOIN t_company b on a.facturer_id=b.companyId where a.agent_id like concat(?,'%') and a.deleted=0 ");
					sb.append(" ) ");
				}else {
					sb.append(" SELECT a.manufacture_id value,a.name name,a.manufacture_id companyId,a.name companyName FROM nes_ope_prod.is_manufacture_info a where a.manufacture_id in ( ");
					sb.append(" SELECT b.nes_companyId from  t_company b  where b.nes_companyId is not null and b.nes_companyId != '' and b.companyId like concat(?,'%')  ");
					sb.append(" ) ");
				}
			}

			items = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
			res.setPage(items);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getNesSbmcList(DataReq req) {
		PageData res = new PageData();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Map<String, Object>> items = new ArrayList<Map<String, Object>>();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		Connection conn = null ;
		try {
			conn = DBUtils.getDBConn_Ope();
			StringBuffer sb = new StringBuffer();
			sb.append(" SELECT sbmc FROM t_sblx ");
			sb.append(" where sbcj='").append(req.getReqData().get("sbcj")).append("'");
			sb.append(" group by sbmc");
			ps = conn
					.prepareStatement(sb.toString(),
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			Map<String, Object> map = null;
			while (rs.next()) {
				map = new HashMap<String, Object>();
				for (int k = 1; k <= columnCount; k++) {
					map.put(rsmd.getColumnLabel(k),rs.getString(k) == null ? "" : rs.getString(k));
				}
				items.add(map);
			}
			res.setPage(items);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getNesSbxhList(DataReq req) {
		PageData res = new PageData();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Map<String, Object>> items = new ArrayList<Map<String, Object>>();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		Connection conn = null ;
		try {
			conn = DBUtils.getDBConn_Ope();
			StringBuffer sb = new StringBuffer();
			sb.append(" SELECT sblx_id sblx,sbxh FROM t_sblx ");
			sb.append(" where sbcj='").append(req.getReqData().get("sbcj")).append("'");
			sb.append(" and sbmc='").append(req.getReqData().get("sbmc")).append("'");
			ps = conn
					.prepareStatement(sb.toString(),
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			Map<String, Object> map = null;
			while (rs.next()) {
				map = new HashMap<String, Object>();
				for (int k = 1; k <= columnCount; k++) {
					map.put(rsmd.getColumnLabel(k),rs.getString(k) == null ? "" : rs.getString(k));
				}
				items.add(map);
			}
			res.setPage(items);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	/**
	 * 获取用户openId
	 * @return
	 */
	public List<Map<String, Object>> getUserOpenId() {
		StringBuffer sb = new StringBuffer();
		List<Map<String, Object>> list = null;
		try{
			sb.delete(0,sb.length());
			sb.append("select a.openid from t_user a where a.openid is not null and a.openid!='' and a.sfsc=0 ");
			list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
		}catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return list;
	}

	public PageData getAppHome(DataReq req) {
		PageData res = new PageData();
		StringBuffer sb = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		Map<String,Object> extData = new LinkedHashMap<String, Object>();
		try {
			String uuid=req.getUinfo().getUuid();
			String id=req.getUinfo().getId();
			//待审批
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT count(0) FROM t_project a  ");
			sb.append(" LEFT JOIN t_work_order_info b ON a.uuid = b.dd_id ");
			sb.append(" LEFT JOIN t_user_project c ON a.id=c.projectId  ");
			sb.append(" WHERE b.auditStatus = '0' AND c.userId=? ");
			int pendingApproval = this.getJdbcTemplate_newg().queryForObject(sb.toString(),new Object[] {uuid},Integer.class);

			//最新消息
			sb.delete(0, sb.length()) ;
			sb.append(" select count(0) from( ");
			sb.append(" select b.uuid,max(a.createTime) maxTime,1 type ");
			sb.append(" from log_operate a  ");
			sb.append(" left join clue b on a.clueId=b.uuid ");
			sb.append(" where b.clueowner=? and b.followupstatus!='03' group by b.uuid ");
			sb.append(" union ");
			sb.append(" select b.uuid,max(a.modifyTime) maxTime,2 type ");
			sb.append(" from log_claim a ");
			sb.append(" left join clue b on a.clueId=b.uuid ");
			sb.append(" where b.clueowner=? and b.followupstatus!='03' group by b.uuid ");
			sb.append(" ) l where l.maxTime<ADDDATE(now(),INTERVAL -3 day) ");
			int latestNews = this.getJdbcTemplate_newg().queryForObject(sb.toString(),new Object[] {id,id}, Integer.class);

			//待整改
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT count(0) FROM t_project a ");
			sb.append(" LEFT JOIN t_project_check b ON a.uuid = b.project_uuid ");
			sb.append(" LEFT JOIN t_user_project c ON a.id=c.projectId  ");
			sb.append(" WHERE b.check_status = '03' AND c.userId=? ");
			int tobeRectified = this.getJdbcTemplate_newg().queryForObject(sb.toString(),new Object[] {uuid},Integer.class);

			//待复检
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT count(0) FROM t_project a ");
			sb.append(" LEFT JOIN t_project_check b ON a.uuid = b.project_uuid ");
			sb.append(" LEFT JOIN t_user_project c ON a.id=c.projectId ");
			sb.append(" WHERE b.check_status = '04' AND c.userId=? ");
			int tobeRechecked = this.getJdbcTemplate_newg().queryForObject(sb.toString(),new Object[] {uuid},Integer.class);

			//项目总数/施工中
			sb.delete(0, sb.length()) ;
			//sb.append(" SELECT count(0) projectTotal,round(SUM(IF(a.project_status='04',1,0))/count(0)*100,2) projectUnder ");
			sb.append(" SELECT count(0) projectTotal,SUM(IF(a.project_status='04',1,0)) projectUnder ");
			sb.append(" FROM t_project a ");
			sb.append(" LEFT JOIN t_user_project c ON a.id=c.projectId  ");
			sb.append(" WHERE c.userId=? ");
			List<Map<String, Object>> list1 = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{uuid});

			//商机总数/转化率
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT COUNT(0) opportunityTotal,round(sum(if(b.id is not null,1,0))/COUNT(0)*100,2) opportunityRate ");
			sb.append(" FROM opportunity a ");
			sb.append(" LEFT JOIN t_project b ON a.uuid=b.opportunity_uuid ");
			sb.append(" WHERE a.opportunityowner=? ");
			List<Map<String, Object>> list2 = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{id});

			//合同总数/回款率
			//'--' contractTotal,'--' contractRate
			extData.put("pendingApproval", pendingApproval);
			extData.put("latestNews", latestNews);
			extData.put("tobeRectified", tobeRectified);
			extData.put("tobeRechecked", tobeRechecked);
			extData.put("projectTotal", list1.get(0).get("projectTotal"));
			extData.put("projectUnder", list1.get(0).get("projectUnder"));
			extData.put("opportunityTotal", list2.get(0).get("opportunityTotal"));
			extData.put("opportunityRate", list2.get(0).get("opportunityRate"));
			extData.put("contractTotal", null);
			extData.put("contractRate", null);

			//动态列表
			sb.delete(0, sb.length()) ;
			sb.append(" SELECT x.operation,x.operationContent,y.username,DATE_FORMAT(x.operationTime,'%Y-%m-%d %H:%i:%s') operationTime from ( ");
			sb.append(" select remark operation,memo operationContent,userid operater,createTime operationTime ");
			sb.append(" from log_operate ");
			sb.append(" where userid=? and remark<>'' AND memo<>'' ");
			sb.append(" union ");
			sb.append(" select visitaddress operation,visitcontent operationContent,userid operater,visittime operationTime ");
			sb.append(" from log_claim ");
			sb.append(" where userid=? and visitaddress<>'' and visitcontent<>'' ");
			sb.append(" )x LEFT JOIN t_user y on x.operater = y.id order by operationTime desc ");
			//分页
			sb = DBUtils.sql_check(sb) ;
			sbfrom.append(sb.toString()) ;
			int idx=sbfrom.indexOf(" from ") ;
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{id,id}) ;
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;
		    List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{id,id});

			res.setExtData(extData);
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	//在线注册
	@SuppressWarnings("unchecked")
	public PageData addRegister(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("在线信息保存失败");
		newg_status = begin_newg();// 开启事物
		param.put("tableName", "t_register");
		PageData sqlInfo =null;
		try {
			sqlInfo = getInsertSQL(req);
			if (sqlInfo.getRetCode() != 0) {
				rollback_newg(newg_status);
				return sqlInfo;
			}
			final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
			final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
			this.getJdbcTemplate_newg().update(sb_field.toString(),listData.toArray());

			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {

		}
		return res;
	}

	@SuppressWarnings("unchecked")
	public PageData onlineReg(DataReq req) {
		PageData res = addRegister(req);
		HashMap<String, Object> param = req.getReqData();
		String password = "123456";
		try{
			//判断是否存在手机号
			PageData res1 = getUserByPhone(req);
			List<Map<String, Object>> extData = (List<Map<String, Object>>) res1.getExtData();
			if(extData!=null && extData.size()>0) {
				res.setRetCode(9);
	            res.setRetMessage("该手机号已被注册!");
	            return res;
			}
			//判断用户名是否被注册
			PageData res2 = getUserByuserName(req);
			List<Map<String, Object>> extData2 = (List<Map<String, Object>>) res2.getExtData();
			if(extData2!=null && extData2.size()>0) {
				res.setRetCode(9);
	            res.setRetMessage("该账号已被注册!");
	            return res;
			}
			//判断验证码是否正确
			String phone = (String) param.get("phone");
			String code = (String) param.get("code");
			//内存查找
			//boolean validCode = ValidCodeUtil.validCode(phone, code);
			//数据库查找
			UserConnDaoImpl userConnDaoImpl=new UserConnDaoImpl();
			long validCode =userConnDaoImpl.VerificationCode(phone,code);
			if(validCode==0) {
				res.setRetCode(9);
	            res.setRetMessage("验证码输入错误!");
	            return res;
			}else if(validCode<0){
				res.setRetCode(9);
	            res.setRetMessage("验证码过期!");
	            return res;
			}

			//判断密码是否为空
			if(!StringUtils.isEmpty((String) param.get("password"))) {
				password = (String) param.get("password");
			}

			//创建公司
			HashMap<String, Object> companyParam = new HashMap<String, Object>();
			companyParam.put("companyName", param.get("companyName"));
			companyParam.put("p_companyId", "0000");
			companyParam.put("p_companyName", "全部");
			companyParam.put("companyType", "02");
			companyParam.put("areacode", "86");
			DataReq companyReq = new DataReq();
			companyReq.setReqData(companyParam);
			PageData saveCompany = saveCompany(companyReq);
			//创建部门
			HashMap<String, Object> departmentParam = new HashMap<String, Object>();
			departmentParam.put("departmentName", "业务部");
			departmentParam.put("p_companyId", saveCompany.getKey());
			departmentParam.put("p_companyName", param.get("companyName"));
			DataReq departmentReq = new DataReq();
			departmentReq.setReqData(departmentParam);
			PageData saveDepartment = saveDepartment(departmentReq);
			if(saveDepartment.getRetCode()!=0) {
				return saveDepartment ;
			}
			//创建用户
			HashMap<String, Object> userParam = new HashMap<String, Object>();
			userParam.put("username", param.get("phone"));
			userParam.put("password",password);//加密密码
			userParam.put("nickName", param.get("companyName"));
			userParam.put("role_id", "419190a4-1a6f-42b9-b4f1-254224c15ad7");
			userParam.put("companyId", saveCompany.getKey());
			userParam.put("departmentId", saveDepartment.getKey());
			userParam.put("phone", param.get("phone"));
			userParam.put("isOnlineReg", "1");
			DataReq userReq = new DataReq();
			userReq.setReqData(userParam);
			PageData saveUser = saveUser(userReq);
			if(saveUser.getRetCode()!=0) {
				return saveUser ;
			}

			//发送手机短信
//			Map<String,String> map = new HashMap<String,String>();
//			map.put("username", (String) param.get("phone"));
//			SMSUtil.sendSMS_AGENT_REG((String) param.get("phone"), map);

		}catch(Exception e){
			e.printStackTrace();
		}

		return res;
	}

	@SuppressWarnings("unchecked")
	public PageData userReg(DataReq req) {
		PageData res = addRegister(req);
		HashMap<String, Object> param = req.getReqData();
		String password = "123456";
		try{
			//判断是否存在手机号
			PageData res1 = getUserByPhone(req);
			List<Map<String, Object>> extData = (List<Map<String, Object>>) res1.getExtData();
			if(extData!=null && extData.size()>0) {
				res.setRetCode(9);
	            res.setRetMessage("该手机号已被注册!");
	            return res;
			}
			//判断用户名是否被注册
			PageData res2 = getUserByuserName(req);
			List<Map<String, Object>> extData2 = (List<Map<String, Object>>) res2.getExtData();
			if(extData2!=null && extData2.size()>0) {
				res.setRetCode(9);
	            res.setRetMessage("该账号已被注册!");
	            return res;
			}
//			//判断验证码是否正确
//			String phone = (String) param.get("phone");
//			String code = (String) param.get("code");
//			//内存查找
//			//boolean validCode = ValidCodeUtil.validCode(phone, code);
//			//数据库查找
//			UserConnDaoImpl userConnDaoImpl=new UserConnDaoImpl();
//			long validCode =userConnDaoImpl.VerificationCode(phone,code);
//			if(validCode==0) {
//				res.setRetCode(9);
//	            res.setRetMessage("验证码输入错误!");
//	            return res;
//			}else if(validCode<0){
//				res.setRetCode(9);
//	            res.setRetMessage("验证码过期!");
//	            return res;
//			}

			//判断密码是否为空
			if(!StringUtils.isEmpty((String) param.get("password"))) {
				password = (String) param.get("password");
			}

			//创建公司
			HashMap<String, Object> companyParam = new HashMap<String, Object>();
			companyParam.put("companyName", param.get("companyName"));
			companyParam.put("p_companyId", "0000");
			companyParam.put("p_companyName", "全部");
			companyParam.put("companyType", "02");
			companyParam.put("areacode", "86");
			DataReq companyReq = new DataReq();
			companyReq.setReqData(companyParam);
			PageData saveCompany = saveCompany(companyReq);
			//创建部门
			HashMap<String, Object> departmentParam = new HashMap<String, Object>();
			departmentParam.put("departmentName", "业务部");
			departmentParam.put("p_companyId", saveCompany.getKey());
			departmentParam.put("p_companyName", param.get("companyName"));
			DataReq departmentReq = new DataReq();
			departmentReq.setReqData(departmentParam);
			PageData saveDepartment = saveDepartment(departmentReq);
			if(saveDepartment.getRetCode()!=0) {
				return saveDepartment ;
			}
			//创建用户
			HashMap<String, Object> userParam = new HashMap<String, Object>();
			userParam.put("username", param.get("phone"));
			userParam.put("password",password);//加密密码
			userParam.put("nickName", param.get("companyName"));
			userParam.put("role_id", "419190a4-1a6f-42b9-b4f1-254224c15ad7");
			userParam.put("companyId", saveCompany.getKey());
			userParam.put("departmentId", saveDepartment.getKey());
			userParam.put("phone", param.get("phone"));
			userParam.put("isOnlineReg", "1");
			DataReq userReq = new DataReq();
			userReq.setReqData(userParam);
			PageData saveUser = saveUser(userReq);
			if(saveUser.getRetCode()!=0) {
				return saveUser ;
			}

			//发送手机短信
//			Map<String,String> map = new HashMap<String,String>();
//			map.put("username", (String) param.get("phone"));
//			SMSUtil.sendSMS_AGENT_REG((String) param.get("phone"), map);
			String isDemoData = (String)param.get("isDemoData");
//			if("1".equals(isDemoData)) {
//				String companyIdDemo="00000016";
//		  	    String useridDemo="615";//模板管理用户id
//		  		String companyType="02";
//		  		String companyIdnew=saveCompany.getKey();
//		  		String companyNamenew= (String)param.get("companyName");
//		  		String useridnew=saveUser.getKey();//新建演示公司管理用户id
//		  		String usernamenew=(String)param.get("companyName");//新建演示公司管理用户id
//				pointsDaoImpl.createDemoData(companyIdDemo,useridDemo,companyType,companyIdnew,companyNamenew,useridnew,usernamenew);
//			}
		}catch(Exception e){
			e.printStackTrace();
		}

		return res;
	}

	//判断手机号是否存在
	public PageData getUserByPhone(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		try {
			StringBuffer sb = new StringBuffer();
			sb.append("select * from t_user a where phone='").append(param.get("phone")).append("'  and sfsc=0 ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
			res.setExtData(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	//判断用户名是否被注册
	public PageData getUserByuserName(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		try {
			StringBuffer sb = new StringBuffer();
			sb.append("select * from t_user a where username='").append(param.get("username")).append("'   and sfsc=0  ");
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
			res.setExtData(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData updatePassword(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("修改密码失败");
		newg_status = begin_newg();// 开启事物
		try {
			StringBuffer sb = new StringBuffer();
			//获取登录用户密码
			sb.delete(0, sb.length());
			sb.append("select password from t_user where id=").append(req.getUinfo().getId());
			String userPassword = this.getJdbcTemplate_newg().queryForObject(sb.toString(), String.class);

			//判断旧密码是否正确
			if(!EncryptedUtil.verify((String) param.get("oldPassword"), userPassword)) {
				res.setRetCode(9);
				res.setRetMessage("原密码输入不正确");
				rollback_newg(newg_status);
				return res;
			}
			//判断新密码与旧密码是否一样
			if(String.valueOf(param.get("oldPassword")).equals(param.get("newPassword"))) {
				res.setRetCode(9);
				res.setRetMessage("新密码与旧密码相同");
				rollback_newg(newg_status);
				return res;
			}
			String newPassword = EncryptedUtil.generate(param.get("newPassword").toString(),req.getUinfo().getUsername().toString());
			sb.delete(0, sb.length());
			sb.append("update t_user set password='").append(newPassword).append("' where id=").append(req.getUinfo().getId());

			int i = this.getJdbcTemplate_newg().update(sb.toString());
			if(i>0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
			}else {
				rollback_newg(newg_status);
			}
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {

		}
		return res;
	}

	public PageData getAgentFacturer(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		try {
			StringBuffer sb = new StringBuffer();
			StringBuffer sbfrom = new StringBuffer();
			StringBuffer sbadd = new StringBuffer();
			if (!StringUtils.isEmpty((String)param.get("key"))){
				sbadd.append(" and ( b.address like '%").append(param.get("key")).append("%' ");
				sbadd.append(" or b.companyName like '%").append(param.get("key")).append("%' ");
				sbadd.append(" or b.legalPerson like '%").append(param.get("key")).append("%' ");
				sbadd.append(" or b.contactNumber like '%").append(param.get("key")).append("%') ");
			}
			String companyType=param.get("companyType").toString();
			sb.append(" SELECT ") ;
			sb.append(" b.companyId,b.companyName,b.address,b.legalPerson,b.contactNumber,b.areacode xzqhdm,c.xzqhmc,b.industry ,d.name industryName,b.businessType ,e.name businessTypeName,b.machineCount,b.industryMemo,b.brand,a.id as agentRelationshipId") ;
			sb.append(" FROM t_agent_facturer a ");
			if("01".equals(companyType)){//厂商
				sb.append(" LEFT JOIN t_company b on a.agent_id=b.companyId and b.sfsc<>'1' ");
				sb.append(" LEFT JOIN t_xzqh c on b.areacode=c.xzqhdm ");
				sb.append(" LEFT JOIN t_param d on b.industry=d.value and d.param_type='82' ");
				sb.append(" LEFT JOIN t_param e on b.businessType=e.value and e.param_type='81' ");
				sb.append(" WHERE a.deleted <>'1' and a.facturer_id = ? ").append(sbadd);
			}else if("02".equals(companyType)){//代理商
				sb.append(" LEFT JOIN t_company b on a.facturer_id=b.companyId and b.sfsc<>'1' ");
				sb.append(" LEFT JOIN t_xzqh c on b.areacode=c.xzqhdm ");
				sb.append(" LEFT JOIN t_param d on b.industry=d.value and d.param_type='82' ");
				sb.append(" LEFT JOIN t_param e on b.businessType=e.value and e.param_type='81' ");
				sb.append(" WHERE a.deleted<>'1' and a.agent_id = ? ").append(sbadd);
			}
			sb.append(" and b.companyId<>'' and b.companyId is not null ");
			//System.out.println(sb);
			//System.out.println(req.getUinfo().getCompanyId());
			sb = DBUtils.sql_check(sb) ;
			sbfrom.append(sb.toString()) ;
			int idx=sbfrom.indexOf(" FROM ") ;
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{req.getUinfo().getCompanyId()}) ;
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;
		    List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getAgentFacturerWdl(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		try {
			StringBuffer sb = new StringBuffer();
			StringBuffer sbfrom = new StringBuffer();
			StringBuffer sbadd = new StringBuffer();
			if (!StringUtils.isEmpty((String)param.get("key"))){
				sbadd.append(" and ( b.address like '%").append(param.get("key")).append("%' ");
				sbadd.append(" or b.companyName like '%").append(param.get("key")).append("%' ");
				sbadd.append(" or b.legalPerson like '%").append(param.get("key")).append("%' ");
				sbadd.append(" or b.contactNumber like '%").append(param.get("key")).append("%') ");
			}
			String companyType=param.get("companyType").toString();
			sb.append(" SELECT ") ;
			sb.append(" b.companyId,b.companyName,b.address,b.legalPerson,b.contactNumber,b.areacode xzqhdm,c.xzqhmc,b.industry,b.businessType,b.machineCount,b.industryMemo,b.brand ") ;
			sb.append(" FROM t_company b ");
			if("01".equals(companyType)){//厂商
				sb.append(" LEFT JOIN t_xzqh c on b.areacode=c.xzqhdm ");
				sb.append(" WHERE b.sfsc<>'1' and b.companyType='02' ").append(sbadd);
				sb.append(" and b.companyId not in(SELECT a.companyId FROM t_company a " + "LEFT JOIN t_agent_facturer b ON b.agent_id = a.companyId and b.deleted<>'1'" + "WHERE a.companyType = '02' and a.sfsc<>'1' and b.facturer_id =? " +
			            "GROUP BY a.companyId ) ");
			}else if("02".equals(companyType)){//代理商
				sb.append(" LEFT JOIN t_xzqh c on b.areacode=c.xzqhdm ");
				sb.append(" WHERE b.sfsc<>'1' and b.companyType='01' ").append(sbadd);
				sb.append(" and b.companyId not in(SELECT a.companyId FROM t_company a " + "LEFT JOIN t_agent_facturer b ON b.facturer_id = a.companyId and b.deleted<>'1'" + "WHERE a.companyType = '01' and a.sfsc<>'1' and b.agent_id =? " +
			            "GROUP BY a.companyId ) ");
			}
			//System.out.println(sb);
			//System.out.println(req.getUinfo().getCompanyId());
			sb = DBUtils.sql_check(sb) ;
			sbfrom.append(sb.toString()) ;
			int idx=sbfrom.indexOf(" FROM ") ;
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{req.getUinfo().getCompanyId()}) ;
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;
		    List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData delAgentFacturer(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("删除参数信息失败");
		newg_status = begin_newg();// 开启事物
		try {
			StringBuffer sb = new StringBuffer();
			sb.append(" UPDATE t_agent_facturer set deleted='1' where 1=1 ");
			String companyType=param.get("companyType").toString();
			if("01".equals(companyType)){//厂商
				sb.append(" and facturer_id='").append(req.getUinfo().getCompanyId()).append("' ");
				sb.append(" and agent_id='").append(param.get("companyId")).append("' ");
			}else if("02".equals(companyType)){//代理商
				sb.append(" and facturer_id='").append(param.get("companyId")).append("' ");
				sb.append(" and agent_id='").append(req.getUinfo().getCompanyId()).append("' ");
			}
			this.getJdbcTemplate_newg().update(sb.toString());
			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {

		}
		return res;
	}

	public PageData addAgentFacturer(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("建立代理关系失败！");
		String manufacturerCompanyId = "";	// 准备记录厂商公司id
		String agentCompanyId = "";			// 准备记录代理商公司id
		newg_status = begin_newg();// 开启事物
		try {
			StringBuffer sb = new StringBuffer();
			sb.append(" INSERT INTO t_agent_facturer (agent_id,facturer_id, gmt_create, gmt_create_id) VALUES (?,?,?,?)");
			String companyType=param.get("companyType").toString();
			if("01".equals(companyType)){//厂商
				this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{param.get("companyId"),req.getUinfo().getCompanyId(), new Date(), req.getUinfo().getId()});
				manufacturerCompanyId = req.getUinfo().getCompanyId();
				agentCompanyId = String.valueOf(param.get("companyId"));
			}else if("02".equals(companyType)){//代理商
				this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{req.getUinfo().getCompanyId(),param.get("companyId"), new Date(), req.getUinfo().getId()});
				agentCompanyId = req.getUinfo().getCompanyId();
				manufacturerCompanyId = String.valueOf(param.get("companyId"));
			}
			commit_newg(newg_status);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {
			// 建立代理关系成功后,补充代理商对厂商的申请记录 - 历史遗留问题,先这样做
			try {
				if (0 == res.getRetCode()) {
					tAgentIntentApplicationService.supplementAgentIntentRecord(manufacturerCompanyId, agentCompanyId);
				}
			} catch (Exception e) {
				log.error("补充代理申请记录失败,失败原因:{}", e.getMessage());
				e.printStackTrace();
			} finally {

			}
		}
		return res;
	}

	public PageData getLogs(DataReq req){
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		try {
			StringBuffer sblist = new StringBuffer();
			StringBuffer sbchart = new StringBuffer();
			StringBuffer sbadd = new StringBuffer();
			StringBuffer sbfrom = new StringBuffer();
			sbadd.append(" WHERE remark is not null and remark <> '' and uid<>'0' ");
			if(!StringUtils.isEmpty((String) req.getReqData().get("dateType"))){
            	String dateType = String.valueOf(req.getReqData().get("dateType")) ;
            	String query = dateTypeCondition_log(dateType);
            	sbadd.append(query);
			}
			if(!StringUtils.isEmpty((String) req.getReqData().get("name"))) {
				sbadd.append(" and b.nickName like '%").append(req.getReqData().get("name")).append("%' ");
			}
			sbadd.append(" and b.companyId like '").append(req.getUinfo().getCompanyId()).append("%' ");
			if(!StringUtils.isEmpty((String) req.getReqData().get("companyId"))) {
				sbadd.append(" and b.companyId='").append(req.getReqData().get("companyId")).append("' ");
			}
			//列表
			sblist.append(" SELECT yhbs,DATE_FORMAT(logdate,'%Y-%m-%d %H:%i:%s') logdate,remark FROM t_logs a inner join t_user b on a.uid=b.id ").append(sbadd);
			sblist.append(" order by logdate desc ");
			sblist = DBUtils.sql_check(sblist) ;
			sbfrom.append(sblist.toString()) ;
			int idx=sbfrom.indexOf(" FROM ") ;
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{}) ;
			sblist.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;
		    List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sblist.toString(), new Object[]{});
		    //图形
		    sbchart.append(" SELECT remark,count(0) cnt FROM t_logs a inner join t_user b on a.uid=b.id ").append(sbadd);
		    sbchart.append(" GROUP BY remark  ");
		    sbchart = DBUtils.sql_check(sbchart) ;
		    List<Map<String, Object>> chart = this.getJdbcTemplate_newg().queryForList(sbchart.toString(), new Object[]{});
			res.setExtData(chart);
		    res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public PageData getUserPic(Connection conn ,DataReq req) throws SQLException {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取用户头像失败");

			String id=req.getUinfo().getId();
			Map<String,Object> map = new HashMap();
			StringBuffer sb = new StringBuffer();
			sb.append("select a.pic	 from t_user a where a.id=").append(id);
			List<Map<String, Object>> list = this.queryForList(conn,sb.toString(),new Object[] {});
			String pic = (String) list.get(0).get("pic");
			if (!StringUtils.isEmpty(pic)) {
                String picReal = OssUtil.getImgSignURL((String) pic, 0);
                map.put("picReal", picReal);
            }
			res.setData(map);
			res.setRetCode(0);
			res.setRetMessage("OK");

		return res;
	}

	public PageData updateMfepuser(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("关联账号失败");
		newg_status = begin_newg();// 开启事物
		try {
			StringBuffer sb = new StringBuffer();
			Map<String, Object> e = new LinkedHashMap<String, Object>() ;
			e.put("qyzzh", param.get("qyzzh")) ;
			e.put("dlyhm", param.get("dlyhm")) ;
			e.put("mm", param.get("mm")) ;

			String tmpStr = HttpUtils.submitDataByDoPostJson(JsonUtil.getStriFromObj(e), CustomConfigurationProperties.getMtpfUrl() +  Globals.MTPF_LOGINURL);
			MtpfToken mtpftoken = (MtpfToken) JsonUtil.getObjFromStr(tmpStr, MtpfToken.class);
			if (mtpftoken == null || mtpftoken.getRetCode() != 0) {
				res.setRetCode(9);
				res.setRetMessage("账号密码错误");
				return res ;
			}

			sb.delete(0, sb.length());
			sb.append("update t_user set mfepuser= ? where id=").append(req.getUinfo().getId());
			int i = this.getJdbcTemplate_newg().update(sb.toString(),new Object[] {JsonUtil.getStriFromObj(e)});
			if(i>0) {
				commit_newg(newg_status);
				res.setRetCode(0);
				res.setRetMessage("OK");
			}else {
				rollback_newg(newg_status);
			}
		} catch (Exception e) {
			e.printStackTrace();
			rollback_newg(newg_status);
		} finally {

		}
		return res;
	}

	public PageData getAd(Connection conn, DataReq req) throws SQLException {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败！");
		StringBuffer sb = new StringBuffer();

		String AdType = (String) req.getReqData().get("AdType");
		if(AdType == null) {
			return res;
		}
		try {
			sb.append("SELECT title,url,imgurl from ad where adtype=? and endTime>now() ");
			List<Map<String, Object>> page = this.queryForList(conn, sb.toString(), new Object[]{AdType});
			res.setPage(page);
			res.setRetCode(0);
			res.setRetMessage("ok");
		} finally {

		}

		return res;
	}

	//发放优惠券记录
	public PageData sendCoupon(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("插入商城优惠卷失败！");
		try {
			//如果是商城优惠卷，插入商城优惠卷
			String shopkey = req.getReqData().get("shopkey").toString();
			String url = Globals.SHOP_COUPONRECEIVE_URL+"/"+req.getReqData().get("couponId").toString();
			Map<String,String> header = new HashMap<String, String>();
			header.put("Authori-Zation", shopkey);
			byte[] body = "".getBytes();
			String sendPost = HttpUtils.sendPost(url,body,header);
			JSONObject jsonObject = (JSONObject) JSONObject.parse(sendPost);;
			Map<String, Object> map = JSONObject.parseObject(jsonObject.toJSONString(), Map.class);
			//如果是商城优惠卷，插入商城优惠卷
			res.setData(map);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception ignored) {
			ignored.printStackTrace();
		} finally {

		}
		return res ;
	}

	public static void main(String[] args) {
		String tmpStr = HttpUtils.submitDataByDoPostJson(Globals.MTPF_LOGINUSER,CustomConfigurationProperties.getMtpfUrl() + Globals.MTPF_LOGINURL) ;
		MtpfToken mtpftoken = (MtpfToken) JsonUtil.getObjFromStr(tmpStr, MtpfToken.class);
		if (mtpftoken != null && mtpftoken.getRetCode() == 0) {
			String token = mtpftoken.getPage().get(0).getToken();
			System.out.println(token);
		}

	}
	public PageData userLoginLogCount(DataReq req)  throws SQLException{
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		try {
			StringBuffer sb = new StringBuffer();
			sb.append(" select remark, LEFT(a.yhbs,LOCATE('-',a.yhbs)-1) yhbs,count(*) cnt,b.NickName,b.phone,c.companyName,b.companyId " +
					" FROM t_logs a \r\n" +
					" INNER JOIN t_user  b on LEFT(a.yhbs,LOCATE('-',a.yhbs)-1)=b.username \r\n" +
					" INNER JOIN t_company c on b.companyId =c.companyId  \r\n" +
					" WHERE remark='登录' and uid = 0 and b.companyId like '").append(req.getUinfo().getCompanyId()).append("%' ");
			if(!StringUtils.isEmpty((String) req.getReqData().get("quyName"))) {
				sb.append(" and (b.nickName like '%").append(req.getReqData().get("quyName")).append("%' or a.yhbs like '%").append(req.getReqData().get("quyName")).append("%'  or b.phone like '%").append(req.getReqData().get("quyName")).append("%') ");
			}

			if(!StringUtils.isEmpty((String) req.getReqData().get("logdateMax"))) {
				sb.append(" and a.logdate<=DATE_ADD('").append(req.getReqData().get("logdateMax")).append("',INTERVAL 1 day) ");
			}
			if(!StringUtils.isEmpty((String) req.getReqData().get("logdateMin"))) {
				sb.append(" and a.logdate>='").append(req.getReqData().get("logdateMin")).append("' ");
			}
			if(!StringUtils.isEmpty((String) req.getReqData().get("companyId"))) {
				sb.append(" and b.companyId = '").append((String) req.getReqData().get("companyId")).append("' ");
			}
			sb.append("  group by LEFT(a.yhbs,LOCATE('-',a.yhbs)-1)  order by LEFT(a.yhbs,LOCATE('-',a.yhbs)-1) ");

				sb = DBUtils.sql_check(sb) ;
			 List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});

		    res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}
	public PageData userLoginLogList(DataReq req) throws SQLException {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取信息失败");
		try {
			StringBuffer sb = new StringBuffer();
			sb.append(" select remark, LEFT(a.yhbs,LOCATE('-',a.yhbs)-1) yhbs,b.NickName,b.phone,DATE_FORMAT(a.logdate,'%Y-%m-%d %H:%i:%s') logdate ,c.companyName,b.companyId,a.ip  " +
					" FROM t_logs a \r\n" +
					" INNER JOIN t_user  b on LEFT(a.yhbs,LOCATE('-',a.yhbs)-1)=b.username \r\n" +
					" INNER JOIN t_company c on b.companyId =c.companyId  \r\n" +
					" WHERE remark='登录' and a.uid = 0 and b.companyId like '").append(req.getUinfo().getCompanyId()).append("%' ");
			if(!StringUtils.isEmpty((String) req.getReqData().get("quyName"))) {
				sb.append(" and (b.nickName like '%").append(req.getReqData().get("quyName")).append("%' or a.yhbs like '%").append(req.getReqData().get("quyName")).append("%'  or b.phone like '%").append(req.getReqData().get("quyName")).append("%') ");
			}

			if(!StringUtils.isEmpty((String) req.getReqData().get("logdateMax"))) {
				sb.append(" and a.logdate<=DATE_ADD('").append(req.getReqData().get("logdateMax")).append("',INTERVAL 1 day) ");
			}
			if(!StringUtils.isEmpty((String) req.getReqData().get("logdateMin"))) {
				sb.append(" and a.logdate>='").append(req.getReqData().get("logdateMin")).append("' ");
			}
			if(!StringUtils.isEmpty((String) req.getReqData().get("companyId"))) {
				sb.append(" and b.companyId = '").append((String) req.getReqData().get("companyId")).append("' ");
			}
			if(!StringUtils.isEmpty((String) req.getReqData().get("yhbs"))) {
				sb.append(" and LEFT(a.yhbs,LOCATE('-',a.yhbs)-1) = '").append((String) req.getReqData().get("yhbs")).append("' ");
			}
			sb.append(" order by a.logdate desc ");


			StringBuffer sbfrom = new StringBuffer();

			sb = DBUtils.sql_check(sb) ;
			sbfrom.append(sb.toString()) ;
			int idx=sbfrom.indexOf(" FROM ") ;
			sbfrom.delete(0, idx);
			Page page = new Page(this.getJdbcTemplate_newg(),sbfrom.toString(),req.getPageid(),req.getPagesize(),res,new Object[]{}) ;
			sb.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize()) ;
		    List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});

		    res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}



	public PageData getRoomUseTypes(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取房间类型失败");
		try {
			StringBuffer sb = new StringBuffer();
			sb.append("select a.id as tid,a.version as tVsersion,a.name as tName,b.id as uid , b.RTid as uRTid,b.version as uVersion,b.name as uName");
			sb.append(" from hy_roomtype a inner join hy_roomuse b on a.Id = b.RTId");
			if(!StringUtils.isEmpty((String)param.get("keyWord"))){
				sb.append(" where a.name like '%").append(param.get("keyWord")).append("%'");
				sb.append(" or b.name like '%").append(param.get("keyWord")).append("%'");
			}
			List<Map<String, Object>> info = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
			List<Map<String, Object>> list = new ArrayList<>();
			List<Map<String, Object>> list_sub = new ArrayList<>();
			String flag = "";
			for(Map<String,Object> p : info){
				if("".equals(flag) || !flag.equals(p.get("tName"))){
					HashMap<String,Object> roomType = new HashMap<>();
					roomType.put("id",p.get("tid"));
					roomType.put("version",p.get("tVsersion"));
					roomType.put("name",p.get("tName"));
					for(Map<String,Object> pp : info){
						if(p.get("tName").equals(pp.get("tName"))){
							HashMap<String,Object> roomUse = new HashMap<>();
							roomUse.put("id",pp.get("uid"));
							roomUse.put("RTid",pp.get("uRTid"));
							roomUse.put("version",pp.get("uVersion"));
							roomUse.put("name",pp.get("uName"));
							roomUse.put("type",1);
							roomUse.put("parentName",p.get("tName"));
							list_sub.add(roomUse);
						}
					}
					roomType.put("type",0);
					roomType.put("submenu",list_sub);
					list.add(roomType);
				}
				list_sub = new ArrayList<>();
				flag = (String)p.get("tName");
			}

			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getLoadParam(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取房间参数失败");
		try {
			StringBuffer sb = new StringBuffer();
			sb.append("select * from hy_roomuse a inner join hy_ruloadparam b on a.id = b.RUid where a.id = ?");
			if(StringUtils.isEmpty((String)param.get("id"))){
				res.setRetMessage("房间ID不能为空");
				return res;
			}
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{param.get("id")});
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;

	}

	public PageData getUsersInCompany(DataReq req) {
		Map<String, Object> param = req.getReqData();
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("查询用户失败");
		StringBuffer sb = new StringBuffer();
		try {
			String companyId = req.getUinfo().getCompanyId();
			String departmentId = (String) param.get("departmentId");
			sb.append("select id,username, nickName from t_user where companyId = ?");
			sb.append(StrUtil.isEmpty(departmentId) ? "" : "and departmentId = ?");
			Object[] condition;
			if (StrUtil.isEmpty(departmentId)) {
				condition = new Object[]{companyId};
			} else  {
				condition = new Object[]{companyId, departmentId};
			}
			List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), condition);
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getMerList(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取商品列表信息失败");
		try {
			StringBuffer sb = new StringBuffer();
			sb.append(" SELECT id mer_id,`name` mer_name FROM eb_merchant WHERE is_del = 0 and is_switch=1 and type_id='14' ");
			List<Map<String, Object>> list = this.getJdbcTemplate_mall().queryForList(sb.toString(), new Object[]{});
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {

		}
		return res;
	}

	public PageData getCcToken(DataReq req) {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("获取token失败");
		JSONObject paramJson = new JSONObject();
		paramJson.put("account", "nesadmin");
		paramJson.put("companyId", "86");
		String url = CallMethodEnum.callCenterToken.url();

		log.info("getCcToken: {}", url);
		String result = HttpUtils.sendJSONPost(url, paramJson.toJSONString());
		JSONObject resultJson = JSON.parseObject(result);
		if (ObjectUtil.isNotNull(resultJson) && 0 == resultJson.getInteger("code")) {
			res.setData(resultJson.getObject("data", Map.class));
			res.setRetCode(0);
			res.setRetMessage("ok");
		}
		return res;
	}

	/**
	 * 根据前端传过来的经纬度，所属公司信息，查询同一行政区划下商城客服的信息
	 *
	 * @param req
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> getNearCSSInfo(DataReq req) throws SQLException {
//		将经纬度转成行政区划代码（xzqhdm）
		TUser tUser = new TUser();
		JSONObject getXzqhdm = MapUtil.getCityName(ParameterUtil.getString(req, "longitude"), ParameterUtil.getString(req, "latitude"));
//        自动生成的断言
		assert getXzqhdm != null : "输入的经纬度无效或异常，请检查!";
		String Xzqhdm = getXzqhdm.getString("towncode");
		tUser.setCompanyId(ParameterUtil.getString(req, "companyId"));
		char[] xzqhdmArr = Xzqhdm.toCharArray();
//		拿到当前行政区划上一级别的行政区划代码(格式:2 2 2 3 3)
		int i = xzqhdmArr.length - 1, j = 0;
		String xzqhdmUp = "";
//		从数组找到最后一个不为0时的下标，用于判断行政区划级别
		while (i >= 0) {
			if (xzqhdmArr[i] != '0') {
				break;
			}
			i -= 1;
		}
//		如果当前的行政区划级别在2级及以下且当前级别没有门店客服信息，就向上查
		if (i >= 3) {
//			给实体类的行政区划代码赋值
			while (j <= i) {
				xzqhdmUp = xzqhdmUp + xzqhdmArr[j];
				j++;
			}
			tUser.setAreacode(xzqhdmUp);
//			使用模糊查询所相关的信息
			List<Map<String, Object>> list = tUserMapper.selectNearCSSInfo(tUser);
			if (list.isEmpty()) {
//			行政区划代码形式2 2 2 3 3 =》ab 级别1 cd 级别2 ef 级别3 ghi 级别4 jkl 级别5
				j = 0;
				xzqhdmUp = "";
//				级别4
				while (j <= 8) {
					xzqhdmUp = xzqhdmUp + xzqhdmArr[j];
					j++;
				}
				tUser.setAreacode(xzqhdmUp);
				List<Map<String, Object>> list2 = tUserMapper.selectNearCSSInfo(tUser);
				if (list2.isEmpty()) {
					j = 0;
					xzqhdmUp = "";
//				级别3
					while (j <= 5) {
						xzqhdmUp = xzqhdmUp + xzqhdmArr[j];
						j++;
					}
					tUser.setAreacode(xzqhdmUp);
					List<Map<String, Object>> list3 = tUserMapper.selectNearCSSInfo(tUser);
					if (list3.isEmpty()) {
						j = 0;
						xzqhdmUp = "";
//				级别2
						while (j <= 3) {
							xzqhdmUp = xzqhdmUp + xzqhdmArr[j];
							j++;
						}
						tUser.setAreacode(xzqhdmUp);
						List<Map<String, Object>> list4 = tUserMapper.selectNearCSSInfo(tUser);
						if (list4.isEmpty()) {
							j = 0;
							xzqhdmUp = "";
//				级别1
							while (j <= 1) {
								xzqhdmUp = xzqhdmUp + xzqhdmArr[j];
								j++;
							}
							tUser.setAreacode(xzqhdmUp);
							List<Map<String, Object>> list5 = tUserMapper.selectNearCSSInfo(tUser);
							return list5;
						}
						return list4;
					}
					return list3;
				}
				return list2;
			}
			return list;
		}
//		如果行政区划级别在2级以上，不再向上级别查询，当list.size()为0时,说明用户附近没有门店及相关信息。
		j = 0;
		xzqhdmUp = "";
		while (j <= i) {
			xzqhdmUp = xzqhdmUp + xzqhdmArr[j];
			j++;
		}
		tUser.setAreacode(xzqhdmUp);
//			使用模糊查询所有相关的信息
		List<Map<String, Object>> list = tUserMapper.selectNearCSSInfo(tUser);
		return list;
	}


	public void processSuperAdminUser(String companyId, SaveBusinessCompanyVo saveBusinessCompanyVo, MallUserInfo mallUserInfo) {
		// 查询是否存在超级管理员用户(roleId=01)
		UserInfo userInfo = selectUserByCompanyId(companyId);
		if (userInfo == null) {
			// 不存在则创建
			String companyName = saveBusinessCompanyVo.getCompanyFullName();

			// 创建超级管理员前, 需要先新增部门
			DepartmentSaveVO departmentSaveVO = new DepartmentSaveVO();
			departmentSaveVO.setDepartmentId("");
			departmentSaveVO.setDepartmentName("商城协作部");
			departmentSaveVO.setParentId(companyId);
			departmentSaveVO.setParentName(companyName);
			departmentSaveVO.setParentType(0);

			Department department = null;
			try {
				department = departmentService.saveDepartment(departmentSaveVO);
			} catch (Exception e) {
				throw new RuntimeException(e);
			}

			// 默认创建一个超管用户
			UserInfo uinfo = new UserInfo();
			uinfo.setUuid(UUID.randomUUID().toString());
			uinfo.setNickName(String.format("默认超管-%s", saveBusinessCompanyVo.getContact()));
			uinfo.setUsername(saveBusinessCompanyVo.getUniformSocialCreditCode()); // 统一信用代码
			uinfo.setPassword(EncryptedUtil.generate("nes@808", uinfo.getUsername()));
			uinfo.setSex("01");
			uinfo.setPhone(saveBusinessCompanyVo.getContactNumber());
			uinfo.setRole_id("01");
			uinfo.setUserType("04");
			uinfo.setCompanyId(companyId);
			uinfo.setDepartmentId(department.getDepartmentId());
			uinfo.setCreateUid("131");

			//添加用户
			insertUser(uinfo);
		}else{
			//已存在超级管理员 则创建普通用户
			UserInfo uinfo = new UserInfo();
			uinfo.setUuid(IdUtils.getIncreaseIdByNanoTime());
			uinfo.setNickName(mallUserInfo.getRealName());
			uinfo.setUsername(mallUserInfo.getAccount());
			uinfo.setPassword(EncryptedUtil.generate(mallUserInfo.getPwd(), uinfo.getUsername()));
			uinfo.setPhone(mallUserInfo.getPhone());
			uinfo.setSex("01");
			uinfo.setRole_id("2241214b-6756-448e-b8d3-2923bc6767ba");
			uinfo.setUserType("04");
			uinfo.setCompanyId(companyId);
			uinfo.setDepartmentId(userInfo.getDepartmentId());
			uinfo.setCreateUid("131");

			//添加用户
			insertUser(uinfo);
		}
	}

	public UserInfo selectUserByCompanyId(String companyId) {
		return this.tUserMapper.selectUserByCompanyId(companyId);
	}

	public void insertUser(UserInfo uinfo) {
		this.tUserMapper.insert(uinfo);
	}
}